Distinguishing between records with some identical data

[33216CLC]

I have a slight problem with how my records are being selected in my Payroll program. If I have two employees with identical Full Names, I am unable to show the records of one of them eventhough both have different id numbers and both names appear in my employee list screen. When I click on either names, the record comes up with the information that refers to the one with the lowest Id number. In both cases the Id number text box shows the lowest id number. Here is the code used for the query:

sqlquery3 = "select * from employee Where ((employee.company_id =" & Company_ID & &quot AND "
sqlquery3 = sqlquery3 & " (employee.[Full Name] ='" & frmEmployeeList.List1.List(frmEmployeeList.List1.ListIndex) & "')) "
Set rs3 = dbPayroll.OpenRecordset(sqlquery3, dbOpenDynaset, dbExecDirect, dbOptimistic)

I tried using select distinct, but there was no difference.


Cooleen

 

[Snaggs]

Cooleen,

See my FAQ. It might help you out.

faq222-315 Snaggs
tribesaddict@swbell.net

 

[swilliams]

Basically, you need to feed the WHERE clause information about the primary key to the table concerned, which by the sounds of it is the EmployeeID, rather than the employee name.

If you are selecting the employee from a combo box, use the AddItem method of the combo box to add the names, but use the ItemData method to add the ID's (Combo1.ItemData(Combo1.NewIndex) = lEmployeeID - where lEmployeeID is a Long holding employee ID's). Then you can get at the employeeID by Combo1.ItemData(Combo1.ListIndex) - providing Combo1.ItemData <> -1 (this case will give an error, so be sure to code around this)

Simon

 

[CybOrg]

An easy way around it is to populate the combo/list box with &quot;SELECT employeeID, Employee_Name etc&quot;, setting the column width of column 1 to 0, and using column 1 as the bound column. Then, when you pick in the list box, what you see is name, but what you get is unique ID.

 

[33216CLC]

Thanks guys,

Cooleen