469,578 Members | 1,863 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

Listing Two Fields

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..
Aug 30 '06 #1
7 2259
MMcCarthy
14,534 Expert Mod 8TB
are you populating your list box with CustID and then CustomerName using CustID as the bound field. I don't follow your code exactly but it looks like you're using a combination of the two. When you try to open a record using this it doesn't recognise the value.

I would normally populate a listbox using a value string:

Expand|Select|Wrap|Line Numbers
  1. Dim strValues As String
  2.  
  3.     lstData.RowSourceType = "Value List"
  4.     lstData.ColumnCount =  2
  5.     lstData.BoundColumn = 1
  6.     ' you can also set the column widths to hide the first column
  7.  
  8.     strValues = ""
  9.  
  10.     If rs.RecordCount = 0 Then
  11.         errormsg = MsgBox("Sorry cannot find any last name starting with " & txtSearch.Text & " . ", , "No Records")
  12.  
  13.         If Len(txtSearch.Text) > 0 Then
  14.             txtSearch.Text = Mid(txtSearch.Text, 1, Len(txtSearch.Text) - 1)
  15.         Else
  16.             Exit Function
  17.         End If
  18.     End If
  19.  
  20.     rs.MoveFirst
  21.  
  22.     Do Until rs.EOF 
  23.         strValues = strValues & rs!CustID & ";" & rs!LastName & ";" 'THIS CODE'
  24.         rs.MoveNext
  25.     Loop
  26.  
  27.     strValues = Left(strValues, Len(strValues) - 1) ' remove last semi colon
  28.     lstData.RowSource = strValues
  29.  
  30.     lstData.Requery
  31.  
  32.  
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..
Aug 31 '06 #2
Hey thanks again I have inserted your code in the List function but when I try running the program I get the error message "Compile Error - Method or Datamember not found" and VB Points to this code
lstdata.RowSourceType = "Value List"

Am not sure if the listbox I am using supports rowsources? Yes I am trying to list two field values in the list box as this is one way I know of that will list customers with multiple last names. Unless you know of another way ?
Aug 31 '06 #3
MMcCarthy
14,534 Expert Mod 8TB
When you say your listbox doesn't support RowSourceType what kind of listbox are you using



Hey thanks again I have inserted your code in the List function but when I try running the program I get the error message "Compile Error - Method or Datamember not found" and VB Points to this code
lstdata.RowSourceType = "Value List"

Am not sure if the listbox I am using supports rowsources? Yes I am trying to list two field values in the list box as this is one way I know of that will list customers with multiple last names. Unless you know of another way ?
Aug 31 '06 #4
It's the standard listbox that comes with Visual Basic 6.0
Sep 2 '06 #5
MMcCarthy
14,534 Expert Mod 8TB
Are you using Access for your frontend or designing your forms directly in Visual Basic?



It's the standard listbox that comes with Visual Basic 6.0
Sep 2 '06 #6
I am using VB Directly. I created the database files using Access and am connecting to the database using VB.
Sep 2 '06 #7
MMcCarthy
14,534 Expert Mod 8TB
Sorry this code is for Access forms VBA. I don't know the properties of the VB listbox. Try blocking out just that line of code. See if that helps.

I am using VB Directly. I created the database files using Access and am connecting to the database using VB.
Sep 3 '06 #8

Post your reply

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

Similar topics

10 posts views Thread by Chris | last post: by
2 posts views Thread by Colin | last post: by
1 post views Thread by JohnM | last post: by
4 posts views Thread by bill..AT..piperb.freeserve.co.uk | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.