Parameters and Drop Down/Combo Box

[Marc]

Hello -

I have a query and report in which I want the user to use a combo box/drop
down box to choose an employee name when the report is run.

The employee name appears many times in the table, and should only appear
once in the combo/drop down.

So, how can I reference the employee name from the table (concatenated from
first and last) to a drop down for the user to choose.

Thanks for any help.

 

[fredg]

Hello -

I have a query and report in which I want the user to use a combo box/drop
down box to choose an employee name when the report is run.

The employee name appears many times in the table, and should only appear
once in the combo/drop down.

So, how can I reference the employee name from the table (concatenated from
first and last) to a drop down for the user to choose.

Thanks for any help.

Set the rowsource of the combo box to:

SELECT Distinct YourTable.FirstName, YourTable.[LastName]
FROM tblBasicData ORDER BY [FirstName];

If the employee name, i.e. John Smith, appears many times in your
table, your database is not properly designed.

Each employee's name should be stored in an Employee table with the
[EmployeeID], [LastName], [Firstname], plus any other needed fields,
i.e. [Department], [SSN], etc., in separate fields.

It should be the unique EmployeeID value which is referred to in any
sub table.

Then the approach would be to base the combo box on the EmployeeID
field, as it's not unusual for a company to have more than one
employee with the same first and last names.

As each employee will have a unique ID number, set the RowSource to:

Select YourTable.EmployeeID, YourTable.[LastName] & ", " &
[FirstName],YourTable.Department] from YourTable Order By [LastName]

Set the column widths to: 0";1.5";0.75"
Set the Bound column to 1
Set the Column Count to 3

Select the employee based upon his name and department.

As criteria for the query, on the EmployeeID criteria line write:
forms!FormName!ComboName