A (hopefully) simple combo box quandry

[AUdby]

I have a combo box that displays an employee's job code (cboJobNumber), of which there are currently six (stored in the tblJobClass table). It used to display each of the six codes on drop down, but now that I have populated the underlying employee table (tblEmployeeData) to its full extent of 53, the combo box displays the job codes for all 53 employees, which is annoying. I've changed around my RowSource SQL statement time and again but still can't seem to fix this problem. Here's the SQL statement:

SELECT tblJobClass.jobNumber, tblJobClass.jobTitle, tblJobClass.salaryGrade
FROM tblJobClass LEFT JOIN tblEmployeeData ON tblJobClass.jobNumber = tblEmployeeData.jobNumber
ORDER BY tblJobClass.jobNumber;

I've tried a right join and inner join as well, but no luck. Is there something that I can add to the Criteria to eliminate repeating values?

Many thanks,
Andreas

 

[Pat Hartman]

The recordsource for the combo box should be the lookup table that defines job codes which I presume is tblJobClass. It should not have the left join to the employee table. The combo box should be bound to the job code on the employee record. The bound field should be job code although you may want to show job title in the combo box. If you delete the control and re-add it with the wizard turned on, it should be built properly.

 

[Robert Saye]

Hi Andreas,

You are making your life way to difficult. First if your form is based on your employee table, then you don't need to add it to your SQL statement. So on your properties page for your combobox set your record source to jobnumber (Since your form is based on employee table it will populate that field), and when you click the query builder next to rowsource all you will select is jobnumber from tblJobClass. That should do it, as long as the two are the same data types (i.e. text, medium date, etc...).

HTH
Robert

 

[AUdby]

Heh! I knew I was overdoing it. Thanks for your help, folks!

Andreas