Listing two fields
--------------------------------------------------------------------------------
Hello all could anybody tell me how I would be able to list information from a database using Two Fields from the Database ? I have this code which will populate the listbox with exactly what I want by when the user clicks the listbox to view the customers information an error message returns "No Current Record"
here's the code I am using to list the two fields from the DB
Private Function list()
If rs.RecordCount = 0 Then
errormsg = MsgBox("Sorry cannot find any last name starting with " & txtSearch.Text & " . ", , "No Records")
If Len(txtSearch.Text) > 0 Then
txtSearch.Text = Mid(txtSearch.Text, 1, Len(txtSearch.Text) - 1)
Else
Exit Function
End If
End If
rs.MoveLast
rs.MoveFirst
Max = rs.RecordCount
rs.MoveFirst
lstdata.Clear
For i = 1 To Max
lstdata.AddItem rs("LastName") & (" ID:") & rs("CusID") 'THIS CODE'
rs.MoveNext
Next i
End Function
At run time the listbox is populated with the Customers Lastname and the Customers ID ' Which is what we want ' However when I try to click the customers last name to display address info phone info etc it keeps giving an error message "No Current Record"
The reason why I am including the CusID is to uniquely indentify each customer as we have some customers with the same last name.
This is the code when I click the listbox to display the customers information.
Private Sub lstdata_Click()
Set rs = db.OpenRecordset("Select * from tbldata where LastName = '" & Trim(lstdata.list(lstdata.ListIndex)) & "'")
rs.MoveFirst 'THE ERROR MESSAGE POINTS HERE. IF CHANGED TO'
'MOVENEXT OR MOVELAST SAME THING HAPPENS '
txtCusNo.Text = rs("CusID")
txtFirstName.Text = rs("FirstName")
txtLastName.Text = rs("LastName")
txtPostal.Text = rs("PostAddress")
txtSuburb.Text = rs("Suburb")
txtCity.Text = rs("City")
txtPhone.Text = rs("Phone")
txtCell.Text = rs("CellContact")
txtEmail.Text = rs("Email")
txtContact.Text = rs("Contact")
txtConMethod.Text = rs("PrefConMethod")
txtJoin.Text = rs("JoinDate")
txtExpiry.Text = rs("ExpiryDate")
txtPurchased.Text = rs("PurchasedItems")
txtProdInterests.Text = rs("ProductInterests")
txtTotal.Text = rs("Total")
cmdEdit.Enabled = True
cmdDelete.Enabled = True
Timer3.Enabled = True
End Sub
The only way I have found for this to work is by listing the customers ID number only and this works fine but I would like to see the customers last name.
Any help would be much appreciated.
Thank You..
VBSourcer
P.S. If you have VB 6.0 and would like to view the project you can do so by downloading the project here http://vbsource.phpnet.us/DBProject.zip
Please send it back to me at sharp2_nz@yahoo.com
I know it's only something so small but I just can't work it out..