Duplicate Values

[vbsourcer]

Hi All,

Could anybody please help me.. Not sure if this was the right forum to post in but this has got to do with Databases so here goes..

Here's the scenario.
We are developing a program In Visual Basic which uses a Microsoft Access Database file to store information of our customers. We have enabled the Allow Duplicates value in the CustomerLastName field as we may have 2 customers with the same last name. Example Sam Edwards and Mike Edwards

Now when we search the database by the customers last name and type in Edwards, the listbox shows that there are two edwards in the database however, when we click the first Edwards it shows Sam Edwards and when we click the second Edwards it still only shows Sam Edwards. I have a feeling it has something to do with the rs.movefirst code as when we change the to rs.movenext it will show Mike Edwards but not Sam Edwards even changing this to rs.movenext makes other records cause this error message " No Current Record."

Am really stuck and would apprecaite anyone's input.

Thanks.
VBSourcer.

[MMcCarthy]

I'm not sure what you've done.

However, populating a Listbox in this way requires the following.

-----------------------------------------------

Dim strSQL As String

Me.ListX.RowSourceType = "Table/Query"

' I assume you get the value 'Edwards from somewhere but since I don't know where
strSQL = "SELECT ID, LastName, FirstName FROM tableName WHERE LastName='Edwards'"

Me.ListX.RowSource = strSQL
Me.ListX.ColumnCount = 3
Me.ListX.BoundColumn = 1
Me.ListX.ColumnWidths = "0 cm; 5 cm; 5 cm"

----------------------------------------

Something along these lines should populate your list correctly. Otherwise, something else is going on.

Hi All,

Could anybody please help me.. Not sure if this was the right forum to post in but this has got to do with Databases so here goes..

Here's the scenario.
We are developing a program In Visual Basic which uses a Microsoft Access Database file to store information of our customers. We have enabled the Allow Duplicates value in the CustomerLastName field as we may have 2 customers with the same last name. Example Sam Edwards and Mike Edwards

Now when we search the database by the customers last name and type in Edwards, the listbox shows that there are two edwards in the database however, when we click the first Edwards it shows Sam Edwards and when we click the second Edwards it still only shows Sam Edwards. I have a feeling it has something to do with the rs.movefirst code as when we change the to rs.movenext it will show Mike Edwards but not Sam Edwards even changing this to rs.movenext makes other records cause this error message " No Current Record."

Am really stuck and would apprecaite anyone's input.

Thanks.
VBSourcer.

[vbsourcer]

Thank You for your prompt response.
We attain the value "Edwards" from our Last Name Search box and this works fine. But the problem is when we have 2 customers both with the last name Edwards
Example Sam Edwards and Mike Edwards

This works fine as the listbox displays all records with whatever last name we tried to search for in this example "Edwards"

But when we click the listbox to display the customer information it will only show the first Sam Edwards record. When we select the second Edwards name from the listbox it still only shows the first Sam Edwards Record. I have a feeling it might have something to do with the rs.movefirst as when this is changed to rs.movenext it will move to the next Edwards record but causes other records to display an error message "No Current Record" here's the code I use to list the data when the user clicks on the listbox

Private Sub lstdata1_Click()
On Error Resume Next
Set rs = Db.OpenRecordset("Select * from tbldata where CusLastName = '" & Trim(lstdata1.list(lstdata1.ListIndex)) & "'")
rs.MoveFirst
lblCardNo.Caption = "Loyalty Card Number: " & rs("GenNum")
txtFirstName.Text = rs("CusFirstName")
txtLastName.Text = rs("CusLastName")
txtPhone.Text = rs("CusPhone")
txtCell.Text = rs("CusCell")
txtEmail.Text = rs("CusEmail")
txtSuburb.Text = rs("CusSuburb")
txtJoined.Text = rs("JoinDate")
txtStamps.Text = rs("SpacesRem")
txtStaff.Text = rs("StaffID")
End Sub

Thanks..

[vbsourcer]

P.S. I am not that advanced at programming but I do know a bit to get me around.

[MMcCarthy]

You have used ListIndex incorrectly this defaults to the first item on the list regardless of selection.

Yours select statement (if the list was referenced correctly would return all customers whose last name is Edwards. Assuming that is what you want try the following. If you only want to return the selected customer you will need to include the customer ID in the list box as a hidden bound field (see example above).

To select all customers with last name edwards:

Private Sub lstdata1_Click()
On Error Resume Next
Set rs = Db.OpenRecordset("Select * from tbldata where CusLastName = '" & Trim(lstdata1.list(lstdata1.Value)) & "'")

rs.MoveFirst
Do until rs.EOF

'Your Code here ...

rs.MoveNext
Loop

End Sub

To select only one customer you will need a way to uniquely identify that customer. CusLastName will not do this.

[vbsourcer]

Thank You again for your help,
I will try your code asap.
Will post reply to let you know how it goes.