Cannot extract values from dictionary once set in VBA/Access 2003

Question

I'm writing a script which pulls out some patient data and generates an XML export.

Each patient record has an associated doctor - but rather than repeat doctor details in each record, I figured I'd set the doctor ID in the patient record, and then include a list of doctors in a different section at the bottom of the document.

One thing I need to do is included a GUID for the doctor in the patient record, but the actual database relationship is a local non-unique ID. I figured the best way forward was to map the GUIDs in a list of local IDs using a dictionary.

Anyway, long story short, here is the bit that builds the require list:

While Not PatientRec.EOF
    Set DoctorRec = MyDB.OpenRecordset("Select Lng_Key, Txt_GUID From Tbl_LU_DoctorDetail Where Lng_Key = " & PatientRec![Lng_Doctor])

    While Not DoctorRec.EOF
        If (IsNull(DoctorRec![Txt_GUID])) Then
            DoctorRec.Edit
            DoctorRec![Txt_GUID] = CreateGUID()
            DoctorRec.Update
        End If

        DoctorList.Add DoctorRec![Lng_Key], DoctorRec![Txt_GUID]

        ' outputs something like '5:{03f50fe1-a0a4-4733-906a-771e22845ea6}
        MsgBox (DoctorRec![Lng_Key] & ":" & DoctorList.Items(DoctorRec![Lng_Key]))

        DoctorRec.MoveNext
    Wend
Wend

' outputs nothing!
MsgBox (DoctorList.Item(5))

' but there is something in there???
MsgBox (DoctorList.count)

I've also tried casting the id to a string using CStr, but get the same result with DoctorList.Item("5")

Worse, when I try:

Dim v As Variant
For Each v In DoctorList.Keys
    MsgBox (v & ":" & DoctorList.Item(v))
Next

I get the error:

Run-time error '3420':

Object invalid or no longer set.

Testing (and the helpfile) indicates that the Variant 'v' is not being set to anything from the Keys property, but the For Each is at least attempting on loop...

-- Update

I found a similar question by someone on vbforums: http://www.vbforums.com/showthread.php?t=622933

I tested with a hardcoded key and item:

DoctorList.Add 5, "String"

The For Each loop now runs once successfully, but then fails with the 3420 error on a second loop (even when it should have stopped on the first loop).

Answer 1

Found the problem - it appears that Dictionaries will happily use objects as keys, so when using the Dictionary.Add method you have to explicitly use the Value property of the field from the recordset:

DoctorList.Add DoctorRec![Lng_Key].Value, DoctorRec![Txt_GUID].Value