Listbox to Access ??

[KellyM]

Hi

I am a bit stuck with the following...

I have an Access DB and would like to access the data through a VB App I have created.
I have the following columns setup in the table:
CustomerName, CustomerID,ItemPurchased, DateofPurchase.

In a listbox in my app I simply want to display the Customer name and when it is clicked bring up the customer record. I have no problems getting to Access through my app. The problem is ensuring that the correct record is selected when the app queries the DB. To get round this I now have listed in the app listbox: CustomerName and CustomerID rather than just CustomerName. In the DB I have created a new colum that contains "CustomerName and CustomerID".(ie. combining 2 tables to form another table, therefore quite amount of duplication) So my program now compares exactly what`s in the listbox to what`s in the new column of the DB.
Is this the only way this can be done through VB? It just seems quite primative / simple??...
Any help or ideas would be greatly appreciated

Thanks in advance

Kelly

[Serge]

If the CustomerId is a numberic type then you can use ItemData property of the ListBox to store it there. Here's a little example. Add a ListBox (List1), Command button (Command1) and a Label (Label1). Make a label big enough to show a whole record for the customer. Also add a reference to Microsoft ActiveX data objects (select the highest version you have installed):

Private m_Conn As New ADODB.Connection

Private Sub Form_Load()
    Dim rs As New ADODB.Recordset

    m_Conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    'Substitute the path with the appropriate one
    m_Conn.Open "C:\myDB.mdb"

    'Substitute YourTable with actual table name
    rs.Open "Select CustomerID, CustomerName From YourTable", m_Conn, adopenStatic

    With List1
        Do Until rs.EOF
            .AddItem rs("CustomerName")
            .ItemDate(.NewIndex) = rs("CustomerId")
            rs.MoveNext
        Loop 
    End With

    rs.Close
    Set rs = Nothing
End Sub


Private Sub Command1_Click()
    Dim rs As New ADODB.Recordset

    rs.Open "Select * from YourTable Where CustomerId = " & List1.ItemData(List1.ListIndex), m_Conn, adOpenStatic

    If Not rs.EOF Then
        With Label1
            .Caption = "CustomerId: " & rs("CustomerId") & vbCrLf
            .Caption = .Caption & "CustomerName: " & rs("CustomerName") & vbCrLf
            .Caption = .Caption & "ItemPurchased: " & rs("ItemPurchased") & vbCrLf
            .Caption = .Caption & "DateOfPurchase: " & rs("DateOfPurchase")
        End With
    End If

    rs.Close
    Set rs = Nothing
End Sub


Private Sub Form_Unload()
    If m_Conn.State = adStateOpen Then m_Conn.Close
    Set m_Conn = Nothing
End Sub