469,323 Members | 1,486 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

Listing Two Access Fields in VB

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 LISTS TWO BOTH FIELDS'
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..
Sep 4 '06 #1
0 1258

Post your reply

Sign in to post your reply or Sign up for a free account.

By using this site, you agree to our Privacy Policy and Terms of Use.