insert data from table to collection vba access

Question

I'm new in VBA access, what I want to do is put data from a table to a collection, but when I do it and check the collection all the rows have the data from the last row I load.

Public Function projectStart() As Collection

    Dim cn As New ADODB.connection

    Dim rs As New ADODB.Recordset
    
    Set cn = CurrentProject.connection
    
    Dim sqlQuery As String

    Set projectStart = New Collection
    
    
    sqlQuery = "select * from p6projects"
    rs.Open sqlQuery, cn
    Do Until rs.EOF
        Dim cashFlow As New cashFlow
        
        cashFlow.letIdproject = rs!id
        cashFlow.letStartDate = rs!startDate
        cashFlow.letBlstartdate = rs!blStartDate
        projectStart.Add cashFlow
        
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    
    cn.Close
    Set cn = Nothing
    
End Function

I'm loading 10 rows from a table, when I print them shows me this, the same data in any rows.

10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019
10 2/6/2019 2/6/2019

How are you printing the records? – Tim Williams Sep 09 '20 at 00:37 — Tim Williams, Sep 09 '20 at 00:37
I use for each next – Alex Aguilar Murillo Sep 09 '20 at 00:54 — Alex Aguilar Murillo, Sep 09 '20 at 00:54

Albert D. Kallal · Answer 1 · 2020-09-09T03:00:32.220

In most cases when you use

rst!StartDate

Well you are returning the FIELD object. This is NOT a value, but a actual DAO object type of field.

In "most" cases, if you are casting to a string, access WILL do this in most cases automatic for you.

but, collections are able to hold strings, or EVEN object types. So, in your code, you are actually adding a refence to the field object and NOT the value.

So, you need to do this:

cashFlow.letIdproject = rs!id.Value
cashFlow.letStartDate = rs!startDate.Value
cashFlow.letBlstartdate = rs!blStartDate.Value

So, now you saving the value to the collection and NOT a reference to the field control. If you save the reference to the field, then it will be whatever row you are on for all instances of that field column reference saved.

Edit: Looking at above, the .value actually should not be required.

To display what we put in the collection?

This would work: cashFlow.letIdproject = rs!ID cashFlow.letStartDate = rs!StartDate cashFlow.letBlstartdate = rs!blStartDate

Dim c As cashFlow
For Each c In projectStart
  Debug.Print c.ID, c.StartDate, c.letBlstartDate

Option Compare Database
Option Explicit
  
Public letIdproject as long
Public letStartDate as date
Public letBlstartdate as date

You have to provide more information as to how your custom class module looks, but the above code approach should work.

Hum, then it might be well how you are displaying the colleciton. i added code to do that — Albert D. Kallal, Sep 09 '20 at 02:53
Add the code that shows your class. See my above additional edit that has a sample class module that would work with the 3 values you have. — Albert D. Kallal, Sep 09 '20 at 03:01

Tim Williams · Accepted Answer · 2020-09-09T01:54:54.830

See VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)

The problem is how you're declaring your class object:

Public Function projectStart() As Collection

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sqlQuery As String
    Dim cashFlow As cashFlow   '<<< declare outside of the loop
    
    Set cn = CurrentProject.Connection
    Set projectStart = New Collection
    sqlQuery = "select * from p6projects"
    rs.Open sqlQuery, cn
    Do Until rs.EOF
        Set cashFlow = New cashFlow  'set inside the loop
        cashFlow.letIdproject = rs!ID
        cashFlow.letStartDate = rs!StartDate
        cashFlow.letBlstartdate = rs!blStartDate
        projectStart.Add cashFlow
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    
    cn.Close
    Set cn = Nothing
    
End Function

It's a good idea to avoid declaring variables As New (when you remember to), and particularly in cases like this.

insert data from table to collection vba access

2 Answers2