0

Does ms access provide hash table like hash{key1}{key2}{key3}[num] in perl? Or any workarounds? I tried below to imitate it but I couldn't add array of recordNum into dType. When I use breakpoint, control can't go into if-clause of If Not dType.exists(rst!serviceType) Then dType.Add rst!serviceType, recordNum(i) End If when i is 1.

Private Sub serviceInfo()

Dim dName As Object
Dim dNum As Object
Dim dType As Object
Dim recordNum(2048) As Integer

Set dName = CreateObject("Scripting.Dictionary") 'Create the Dictionary
Set dNum = CreateObject("Scripting.Dictionary") 'Create the Dictionary
Set dType = CreateObject("Scripting.Dictionary") 'Create the Dictionary

Set dbs = CurrentDb
qStr = "SELECT yearMonth, clName, certiNum, chName, chDateBirth, chNum, serviceType, serviceName " & _
       "FROM tblList " & _
       "WHERE tblList.chName=" & "'" & Me.Form.fchName & "';"

Set rst = dbs.OpenRecordset(qStr)

If Not (Err.Number = 0) Then ' if error
    MsgBox "An error occured (Error Number " & Err.Number & _
      ": " & Err.Description & ")"
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub
ElseIf rst.BOF And rst.EOF Then
    cantFindRecordYoyang = 1
    'rst.Close
End If

With rst

        Dim i As Integer
        Do Until rst.EOF

            recordNum(i) = assetServiceTime(rst!serviceName) / 60

            If Not dType.exists(rst!serviceType) Then
                dType.Add rst!serviceType, recordNum(i)
            End If
            If Not dType.exists(rst!chNum) Then
                dNum.Add rst!chNum, dType
            End If

            If Not dType.exists(rst!chName) Then
                dName.Add rst!chName, dNum
            End If

            i = i + 1

        Loop ' // End do

End With
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
Jeung Hun Han
  • 69
  • 8

1 Answers1

0

You are not moving the recordset, and you may have to be more explicit:

    Dim i As Integer

    Do Until rst.EOF
        recordNum(i) = assetServiceTime(rst!serviceName) / 60

        If Not dType.exists(rst!serviceType.Value) Then
            dType.Add rst!serviceType.Value, recordNum(i)
        End If
        If Not dType.exists(rst!chNum.Value) Then
            dNum.Add rst!chNum.Value, dType
        End If
        If Not dType.exists(rst!chName.Value) Then
            dName.Add rst!chName.Value, dNum
        End If

        i = i + 1
        rst.MoveNext
    Loop 
    rst.Close
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I haven't written comments. Sorry about that. As you said I need to be more explicit. For example, the one that I wanted to make hash table was recoreNum("Mark")("123")("kType")(0), recordNum("Mark")("123")("kType")(1)... and so on. i increases to a certain number. I don't know how to make hash table like this. – Jeung Hun Han Jun 19 '17 at 10:56
  • I meant _explicit code_. Access doesn't offer hash functions nor tables, just tables, so if you wish to create a hash table/field, it is entirely up to you to choose your preferred method. – Gustav Jun 19 '17 at 11:39
  • Gustav, my apologies. I am not a native English speaker, so I didn't understand what you said. But I found that the code I wrote is totally not for like someType("key1")("key2")("key3"). And I think you mean I have to make any method, but still I can't find the way to do it. – Jeung Hun Han Jun 20 '17 at 08:25
  • You just need to search for it. I found this using Bing: [Using hash tables in VBA](https://social.msdn.microsoft.com/Forums/en-US/355cde47-3538-4d59-b53c-35179985386c/using-hash-tables-in-vba?forum=isvvba) and here at SO: [Hash Table/Associative Array in VBA](https://stackoverflow.com/questions/1309689/hash-table-associative-array-in-vba). – Gustav Jun 20 '17 at 09:05
  • I made several hash arraies with several Dictionaries (not in a class) and extract and access from/to them respectively according to your comments because I am not good at VB(A) and oop. Anyway many thanks for your comments. – Jeung Hun Han Jun 23 '17 at 01:27
  • Great! Thanks for the feedback. Have a nice weekend. – Gustav Jun 23 '17 at 07:11