Using Combo Box as Parameter for Report

[JWeaver]

I have a report that has a query as it's control source. The query is
designed to prompt the user for the name of an employee before proceeding.
Right now it is set up to have user enter the last name in one box and the
first name in another box. However, if the user doesn't enter the name
exactly as it is stored in the database then nothing shows up on report.

What I want is for the user to be able to pick the name of the employee from
a combo box or drop down box so that there will be no problems like this in
the future.

I have different reports in other databases that this would come in handy
for and want to be able to use in them as well.

Your help is greatly appreciated!

 

[Jeff Boyce]

A common question here in the reports newsgroup...

Create a form with a combobox that lists the employees.

Modify your query to point to the form for the selection criterion, with
something like:
Forms!YourForm!cboYourCombobox

Add a <Run Report> command button on the form ... and set the action to open
the report that's based on the query.

NOTE: this approach won't work if the form isn't open (Access needs to see
the control on the open form to get the value).

By the way, what do you do when you have two employees named "Smith, John"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JWeaver]

Thanks, Jeff!

I already have a combo box set up on some of my Forms to select the employee
name so I can use it for those.

I have never used command buttons before to print reports so if I have any
problems I will post back. I always set up my reports to pull information
based on parameters in the query. I didn't know you could set this up in a
Form.

To answer your question - If I have more than one employee with the same
name, I either add the middle initial after the first name or, as I have now,
I have 2 employees with the same name but one is a Sr. so I added "Sr" after
his last name to distinguish his from the other one (his son).

 

[Jeff Boyce]

OK, that's how YOU distinguish them. When your users pull up the list of
employees, will THEY be able to distinguish folks with the "same" name?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JWeaver]

I see your point, however, I am the only one who uses a couple of these
databases so this won't be an issue. For the ones that others also use, I
suppose I need to add the middle name to the combo box that I need to create.

I was looking at adding your suggestion to one of my queries/forms and see
that I use a combo box to select the employee number, then it automatically
fills in the boxes for the last name and first name. Would I still be able
to use your code, but need to add column numbers to it to pull up the name
correctly?

Thanks for your help!

 

[Jeff Boyce]

That would really depend on how your query is set up. If this were mine,
I'd use the ID rather than the name to "order" the report.

Regards

Jeff Boyce
Microsoft Office/Access MVP