Combo box jumps to values when duplicates

Hi,

Can someone please help? I have never seen this happening before:

I have a combo box on a form whose source is the following query:

SELECT [tblEmployees].[EmpNumber], [tblEmployees].[Surname]
FROM tblEmployees LEFT JOIN tblLaserscanmessungen_test ON
[tblEmployees].[EmpNumber]=[tblJobs].[EmpNumber]
WHERE ((Not ([tblJobs].[EmpNumber]) Is Null) And (Not
([tblMeasurementsType1].[BodySize]) Is Null And Not
([tblMeasurementsType1].[BodySize])=IsEmpty([BodySize])))
ORDER BY [tblEmployees].[EmpNumber] DESC;

What is happening here is that I display a list of Employees as
follows:

EmpNumber Surname
--------- --------
615 Smith
616 Jones
617 Smith
618 Brown

When I click on the combobox dropdown arrow, the above list displays
correctly. The value that is displayed in the combo box when I click
one of the entries is ONLY the EmpNumber.

The problem is e.g. when I select "615 Smith" (where there are more
than one "Smith" but they have unique EmpNumber's) then after clicking,
the value displayed automatically changes to "617".

I cannot figure out why this is happening, anybody?

Regards,
Jean

Re: Combo box jumps to values when duplicates

Suggestions:

1. Try adding a DISTINCT to the SELECT statement, i.e.:
SELECT DISTINCT [tbl...

2. Any code in the AfterUpdate event of the combo?
If so, try temporarily deleting it.

3. If neither of these solve the problem, check which version of JET you
have on your computer. In the Windows Explorer, search for msjet40.dll
(assuming Access 2000 or above). Typically it is in windows\system32.
Right-click, and choose Properties. On the Version tab, you should see:
4.0.8xxx.0
The numbers at xxx do not matter, but if you do not see the 8, download SP8
for JET 4 from:
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jean" wrote in message
news:1113399965.032629.138390@o13g2000cwo.googlegroups.com.. .
> Can someone please help? I have never seen this happening before:
>
> I have a combo box on a form whose source is the following query:
>
> SELECT [tblEmployees].[EmpNumber], [tblEmployees].[Surname]
> FROM tblEmployees LEFT JOIN tblLaserscanmessungen_test ON
> [tblEmployees].[EmpNumber]=[tblJobs].[EmpNumber]
> WHERE ((Not ([tblJobs].[EmpNumber]) Is Null) And (Not
> ([tblMeasurementsType1].[BodySize]) Is Null And Not
> ([tblMeasurementsType1].[BodySize])=IsEmpty([BodySize])))
> ORDER BY [tblEmployees].[EmpNumber] DESC;
>
> What is happening here is that I display a list of Employees as
> follows:
>
> EmpNumber Surname
> --------- --------
> 615 Smith
> 616 Jones
> 617 Smith
> 618 Brown
>
> When I click on the combobox dropdown arrow, the above list displays
> correctly. The value that is displayed in the combo box when I click
> one of the entries is ONLY the EmpNumber.
>
> The problem is e.g. when I select "615 Smith" (where there are more
> than one "Smith" but they have unique EmpNumber's) then after clicking,
> the value displayed automatically changes to "617".
>
> I cannot figure out why this is happening, anybody?
>
> Regards,
> Jean

Re: Combo box jumps to values when duplicates

Thanks for the reply Allen.

I tried something else, namely changing the BoundColumns property of
the ComboBox from 2 down to 1.
Can't believe it was something like that.

Regards
Jean