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

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 th开发者_C百科e 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).

---

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

---

In regard to GUIDs, Access doesn't like them. Michael Kaplan wrote about this years and years ago. You might want to look into the StringFromGUID() and GUIDToString() functions.

And if there is not an external requirement that you use GUIDs, you should seriously consider getting rid of them entirely. They don't add anything at all that is necessary in 99.99% of Access applications.

Secondly, I've never used the scripting runtime's dictionary, but it really looks to me like it offers nothing you can't already get with a VBA custom collection. Can you outline what you're using it for, and how it is superior to the VBA collection? Also, why are you using early binding and not late binding?