CODEWITHSUNDEEP
ms-accessvba
jedu
jedu

Reputation: 1331

MS Access collection object value is being overwritten

I have a global collection object in a form that I am using to store IDs.

Dim newCollection As New Collection

When a user hits a btn

Private Sub btn_Click()
Dim key As Variant
    newCollection.ADD Me.ID, CStr(ID)

    For Each key In newCollection
        Debug.Print key 
    Next key
End Sub

When a user is at ID 1 in the forms and presses the button above I get the expected 1 returned on my immediate window

But when A user moves to another record in the form let's say from ID 1 to 2 using the forms navigation button

For Some reason the Debug.Print key returns 

2
2

rather than the expected

1
2

For Some reason the key is not overwritten but the value is. What I mean is that

On my immediate console:

?newCollection(1)

returns 2

And

?newCollection(2)

also returns 2

I don't know why this is happening

Upvotes: 1

Views: 55

Answers (1)

HansUp
HansUp

Reputation: 97101

Change this ...

newCollection.ADD Me.ID, CStr(ID)

to this ...

newCollection.ADD Me.ID.Value, CStr(ID)

In the first case, you're adding a field object. So when you later Debug.Print key, you're printing the current value of that field. (That may make more sense if you do Debug.Print TypeName(key) temporarily.)

You need to add the field's value instead of the field itself.

Upvotes: 3

Related Questions