Populating a Collection VBA MS Access - Reference Issue

Question

I have an issue when populating a collection in VBA in MS Access.

Here's the code I have:

Private Sub loadInfo()
    Dim sql As String
    sql = "SELECT * FROM table2';"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(sql)

    With rs
        .MoveFirst
        While rs.EOF = False

            Dim person As New person
            Dim idToString As String
            idToString = rs.Fields("ID").value

            person.setFirstName (rs.Fields("first_name").value)
            person.setLastName (rs.Fields("last_name").value)

            people.Add person, idToString

            .MoveNext
        Wend
    End With
End Sub

The collection is declared an initialized at the top of the class module. I can add items to it just fine. However, at the end of the Sub, I end up with a collection with the right amount of objects (however many I have in the Recordset,) but all of them with the same firstName and lastName.

Now, I thought that every time the "when" loop goes 'round, the variables inside of it would go out of scope, thus becoming null. In this case, that does not seem to be the case. Every time it gets back to person.setFirstName, the firstName of the item already in the collection changes to whatever the current firstName of the new row is. The same goes for lastName.

This leads me to believe that person is not being created every time the loop goes 'round, giving me a collection of similar entries, aside from their key which is unique.

Any ideas as to how to get the loop to perform as it should?

I would move the variable declarations that are inside of the loop to the top of the function. I would substitute that with `Set person = new Person`. — DiegoAndresJAY, Aug 05 '15 at 00:51
Related: https://stackoverflow.com/questions/42656468/whats-the-difference-between-dim-as-new-vs-dim-set — Andre, Apr 09 '19 at 17:44

JCro · Answer 1 · 2015-08-05T01:26:24.367

0

A collection is key-value paired list. It cannot have multiple dimensions.

Try using User Defined Types:

In module declarations...

Public Type Person
    FirstName As String
    LastName As String
End Type

In form declarations...

Dim typPerson() As Person

Then use like this...

ReDim typPerson(0 To rs.RecordCount - 1) As Person

Dim i As Integer
Do While Not rs.EOF
    With typPerson(i)
        .FirstName = rs!FirstName
        .LastName = rs!LastName
    End With
    i = i + 1
    rs.MoveNext
Loop

edited Aug 05 '15 at 01:26

answered Aug 05 '15 at 01:19

JCro

686
5
10

What you're explaining is more or less what I'm doing by declaring a new object based on class module `person`. – cornopaez Aug 05 '15 at 11:20

score 0 · Answer 2 · answered Apr 09 '19 at 15:16

0

In your sub loadinfo(), insert the line: “Set Person=Nothing”, before “.MoveNext”. If you don’t, the same item gets added to the collection the whole time. HTH

answered Apr 09 '19 at 15:16

AJ Jordaan

3
4

Populating a Collection VBA MS Access - Reference Issue

2 Answers2