how to search a word in a two-dimensional array based on my textbox with vba code in MS Access form

Question

I have an array like this:

jack,33500;luci,36120;andro,33941;jenifer,25980;ivanka,12500

I want to find for instance "andro" when I write 33941 in my textbox.
Note: I found out many code in other programming method, but I need to get code for using in MS Access.

My codes is like below. when i click in listbox my array is built and at the same time I expect showing result but it apparently get back an error (substring out of range) codes:

Private Sub txNumber1_Click()

    Dim CurMobile As String, curMesssage As String, strMobiles As String, strMesssages As String, strMM As String
    Dim rowNum As Variant

    Dim TestArrayMobile() As String
    Dim TestArrayMessage() As String
    Dim arr() As String
    Dim i As Integer
    Dim j As Integer

    ' Get selected phone numbers & names
    With txNumber1
        For Each rowNum In .ItemsSelected
             CurMobile = .Column(1, rowNum)
             curMesssage = .Column(3, rowNum)
             strMM = strMM + ";" + curMesssage + "," + CurMobile
             arr() = Split(strMM, ";")

               For j = LBound(arr) To UBound(arr)
'               MsgBox arr(j, 1), , arr(j, 0)
                MsgBox arr(j, 1)
'                      If StrComp(.Column(1, rowNum), arr(j, 1), vbTextCompare) = 0 Then
'                        MsgBox arr(j, 0)
'                        Exit For
'                      End If
                Next j

        Next rowNum
    End With

End Sub

Why an array? Is there not a table with this info? If you want to 'search' a virtual set of data, should probably use a collection object instead of array. With an array, have to loop through the array elements until match is encountered. — June7, Jun 23 '20 at 07:18
ma data come back via web service In the form of an array like above and I need to make apart one of numbers and save name and other things from inside table and insert them into a table — majid, Jun 23 '20 at 08:48
Review https://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array — June7, Jun 23 '20 at 15:40
Is data received as an array or is it just a string? What you show looks like a string. — June7, Jun 23 '20 at 21:40
If you need to compare value in textbox with array, why do you compare with listbox item? — June7, Jun 24 '20 at 16:26
If array is from web service, why does code show building string from listbox then splitting that into array? Not making sense. — June7, Jun 24 '20 at 17:43

score 0 · Answer 1 · answered Jun 23 '20 at 16:11

0

Consider:

Sub GetData(stringToBeFound As String, arr As Variant)
Dim i As Long
For i = LBound(arr) To UBound(arr)
    If StrComp(stringToBeFound, arr(i, 1), vbTextCompare) = 0 Then
        Debug.Print arr(i, 0)
        Exit For
    End If
Next i
End Sub

In place of Debug.Print, can write data to controls on form or run INSERT action SQL.

answered Jun 23 '20 at 16:11

June7

19,874
8
24
34

I already made comments about your code. Edit question to show sample of exactly how data is listed in listbox. Post your code in your question, not an answer. – June7 Jun 25 '20 at 16:29

how to search a word in a two-dimensional array based on my textbox with vba code in MS Access form

1 Answers1