ItemData How to create string array

Hi,

I populate a combobox with the client's firstname and now i am trying to place an alphanumeric record as the index. The field is the client's id. I know that itemData does not support anything but long. Does anyone have a sample that show how the code looks like if an array is created in order to work around that issue. Here is my code below:


'''''The line below is causing the error, i need to be able to create an array like it has been mentioned in previous articles            
 
              cmbName.ItemData(cmbName.NewIndex) = Combo1.ListIndex

Thanks
Andre

Full code


Dim dbs As ADODB.Connection
    Dim rs   As ADODB.Recordset
    Dim firstname As String
    Dim LastName As String
    Set dbs = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
   
    With dbs
        .CursorLocation = adUseClient
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open "C:\Silveus\Forms\Test\CropInsMgmt.mdb", "admin", ""
    End With
   
     With rs
        .Open "SELECT FIRSTNAME, CustomerId FROM tblCustomers", dbs, adOpenForwardOnly, adLockOptimistic
        If Not .BOF Then
           .MoveFirst
            Do While Not .EOF
              cmbName.AddItem !firstname
 
'''''The line below is causing the error, i need to be able to create an array like it has been mentioned in previous articles            
 
              cmbName.ItemData(cmbName.NewIndex) = Combo1.ListIndex
            .MoveNext
            Loop
       
       End If
        .Close
    End With
    dbs.Close
'LoadNames
           
frmNames.WindowState = 2
virgilarAsked:
Who is Participating?
 
PePiConnect With a Mentor Commented:
you can also set the style of cboName to 2 then:

Dim dbs As ADODB.Connection
    Dim rs   As ADODB.Recordset
    Dim firstname As String
    Dim LastName As String
    Set dbs = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
   
    With dbs
        .CursorLocation = adUseClient
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open "C:\Silveus\Forms\Test\CropInsMgmt.mdb", "admin", ""
    End With
   
     With rs
        .Open "SELECT FIRSTNAME, CustomerId FROM tblCustomers", dbs, adOpenForwardOnly, adLockOptimistic
        While Not .EOF
             cmbName.AddItem !firstname & Space(50) & !CustomerID

            .MoveNext
        Wend
        .Close
    End With

    dbs.Close
    Set dbs = Nothing
    Set rs = Nothing

you then create a function for retrieving the Cient ID like so

Private Function GetClientID(cID as String) As String
' cID is = cmbName.Text
'
     GetClientID = Mid(cID, InStrRev(cID, " ") + 1, Len(cID) - InStrRev(cID, " "))
End Function


so when you do a click on the cmbName you go...

Private Sub cmbName_Click()
    If cmbName.ListIndex = -1 Then Exit Sub

    Dim varClientID as String

    varClientID = GetClientID(cmbName.List(cmbName.ListIndex))
End Sub


Hope this will solve your problem.

0
 
PePiCommented:
when do you populate your Combo1 ? I suggest you do it this way...

Dim dbs As ADODB.Connection
    Dim rs   As ADODB.Recordset
    Dim firstname As String
    Dim LastName As String
    Dim i as Integer
    Set dbs = New ADODB.Connection
    Set rs = New ADODB.Recordset
   
   
    With dbs
        .CursorLocation = adUseClient
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Open "C:\Silveus\Forms\Test\CropInsMgmt.mdb", "admin", ""
    End With
   
     With rs
        .Open "SELECT FIRSTNAME, CustomerId FROM tblCustomers", dbs, adOpenForwardOnly, adLockOptimistic
        While Not .EOF
             cmbName.AddItem !firstname
             cmbName.ItemData(cmbName.NewIndex) = i
             Combo1.AddItem !CustomerID

            .MoveNext
            i = i + 1
        Wend
        .Close
    End With

    dbs.Close
    Set dbs = Nothing
    Set rs = Nothing

'LoadNames
           
frmNames.WindowState = 2



0
 
virgilarAuthor Commented:
I guess what i am trying to accomplish is populate cmbNames with the client's name and the client's id as the index so i can do a search based on the client's id. The problem is, the clients id is text and the cmbName.ItemData ONLY works with long or integer. I know that there is a way around that by creating a string but i do not know how to do it.

I do not have to populate combo1 with the customer id. The reason i populate combo1 is because i was trying to read the id from there as i selected the name on the cmbName. That did not work.

So i am looking for a way to store the customerid as the index in cmbName. (customerid is a string, that is the problem)

Andre
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.