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?
精彩评论