473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2399
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

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

Similar topics

1
by: work4u | last post by:
Hello, I am working on a page which listing all product in one page, by should be in 2 column rather than just one. PS: i can do show up all products in 1 column by do while not loop,... ...
10
by: Chris | last post by:
Hi, Not sure if this is the right forum, but hopefully someone can help me. I am creating something for our intranet and i want to list the files and folders of a directory, i found some code...
2
by: Colin | last post by:
I have a table, which is a list of courses with a CourseID key field and about a dozen fields describing the courses. I also have a table that lists all of the students taught by one teacher, which...
1
by: JohnM | last post by:
Thanks for the replies regarding adding fields on the fly. .. I'm now managing that. I can't seem to do something that ought to be more simple, that is to give the user a list of current fields in...
4
by: bill..AT..piperb.freeserve.co.uk | last post by:
I have used code from MS 198755 to create a module that lists current users of the database. Works fine as long as db isnot password potected. How do I adapt code to enter password? This is...
2
by: vbsourcer | last post by:
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...
0
by: vbsourcer | last post by:
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...
8
by: PW | last post by:
Hi, There is code in Alison Balter's excellant "Mastering Access 2003" to create a list of error codes and descriptions but it only generates error messages 3 through 94. Is there a website...
8
by: Swizylstik | last post by:
I'm a newbie to ASP and databases. I have created a simple contact list database and an asp page that shows the contact listing. So far so good. Here's what I have:...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.