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.T ext) > 0 Then
txtSearch.Text = Mid(txtSearch.T ext, 1, Len(txtSearch.T ext) - 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.OpenRecordse t("Select * from tbldata where LastName = '" & Trim(lstdata.li st(lstdata.List Index)) & "'")
rs.MoveFirst 'THE ERROR MESSAGE POINTS HERE. IF CHANGED TO'
'MOVENEXT OR MOVELAST SAME THING HAPPENS '
txtCusNo.Text = rs("CusID")
txtFirstName.Te xt = rs("FirstName" )
txtLastName.Tex t = 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.Te xt = rs("PrefConMeth od")
txtJoin.Text = rs("JoinDate")
txtExpiry.Text = rs("ExpiryDate" )
txtPurchased.Te xt = rs("PurchasedIt ems")
txtProdInterest s.Text = rs("ProductInte rests")
txtTotal.Text = rs("Total")
cmdEdit.Enabled = True
cmdDelete.Enabl ed = 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
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.T ext) > 0 Then
txtSearch.Text = Mid(txtSearch.T ext, 1, Len(txtSearch.T ext) - 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.OpenRecordse t("Select * from tbldata where LastName = '" & Trim(lstdata.li st(lstdata.List Index)) & "'")
rs.MoveFirst 'THE ERROR MESSAGE POINTS HERE. IF CHANGED TO'
'MOVENEXT OR MOVELAST SAME THING HAPPENS '
txtCusNo.Text = rs("CusID")
txtFirstName.Te xt = rs("FirstName" )
txtLastName.Tex t = 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.Te xt = rs("PrefConMeth od")
txtJoin.Text = rs("JoinDate")
txtExpiry.Text = rs("ExpiryDate" )
txtPurchased.Te xt = rs("PurchasedIt ems")
txtProdInterest s.Text = rs("ProductInte rests")
txtTotal.Text = rs("Total")
cmdEdit.Enabled = True
cmdDelete.Enabl ed = 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
Comment