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