By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 2,960 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

Listing two fields

P: 17
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
Aug 29 '06 #1
Share this Question
Share on Google+
2 Replies


P: 17
If you have visual basic 6.0 You can download my sample project which visually shows you where I am going wrong. If you could PLEASE send me back your alterations to sharp2_nz@yahoo.com or just e-mail me the project with your alterations.

To download the source code please visit
http://vbsource.phpnet.us/DBProject.zip

Thanks Again
VBSourcer
Aug 29 '06 #2

P: 17
P.S. You may need to reference Microsoft DAO 3.51 Object Library
Aug 29 '06 #3

Post your reply

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