?
Solved

ItemData How to create string array

Posted on 2005-04-12
3
Medium Priority
?
161 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:virgilar
  • 2
3 Comments
 
LVL 6

Expert Comment

by:PePi
ID: 13767262
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
 

Author Comment

by:virgilar
ID: 13772081
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
 
LVL 6

Accepted Solution

by:
PePi earned 2000 total points
ID: 13774166
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question