List of values for a report parameter

[Chris]

Hi,
I have a problem with an Access report that I am using. The report is
used to return all of the projects thats are associated with a member
of staff.

I have set it up so that when it is run the user has to enter the
staff number of the person they want the dreport based on and then all
that perons records are returned in the report.

The problem with this is that the user may not know the persons
employee number, only their name so what I would like to do is instead
of them having to type the empno in, have a list of values containing
all employees full names and empno's.

When the employee is selected, I want the empno to be inserted into
the parameter that runs the reports.

Is there a way of doing this?
Chris

 

[fredg]

Hi,
I have a problem with an Access report that I am using. The report is
used to return all of the projects thats are associated with a member
of staff.

I have set it up so that when it is run the user has to enter the
staff number of the person they want the dreport based on and then all
that perons records are returned in the report.

The problem with this is that the user may not know the persons
employee number, only their name so what I would like to do is instead
of them having to type the empno in, have a list of values containing
all employees full names and empno's.

When the employee is selected, I want the empno to be inserted into
the parameter that runs the reports.

Is there a way of doing this?
Chris

Create an unbound form.
Add a combo box.
Set the Row Source of the combo box to include the
EmployeeID field and the Employee Name.
Name the Combo Box 'FindEmployee'.
Set it's Bound column to 1.
Set it's Column Count property to 2.
Set the Column Width property to 0";1"

Add a Command Button to the form.
Code the button's click event:

Me.Visible = False

Name this form 'ParamForm'.

Create a query that will return all of the records (you may already
have one).
In the Query's [EmployeeID] field criteria line write:
forms!ParamForm!FindEmployee

Make this query the report's record source

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When ready to run the report, open the report.
The form will open and wait for the selection of the Employee wanted.
Click the command button and then report will run.
When the report closes, it will close the form.

 

[Chris]

Hi, thanks for the help.

I have tried what you have suggested but it doesnt work at the stage
where I have coded the

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog


Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When trying to run the report it is saying that it doesnt find the
macro DoCmd as it saying that the macro doesnt exist. Do I have to
create a new macro do what I want? also can you explain the
"DoCmd.OpenForm "ParamForm", , , , , acDialog " to me plase?

Kind Regards,
Chris

 

[fredg]

Hi, thanks for the help.

I have tried what you have suggested but it doesnt work at the stage
where I have coded the

Next, code the report's Open event:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Code the report's Close event:
DoCmd.Close acForm, "ParamForm"

When trying to run the report it is saying that it doesnt find the
macro DoCmd as it saying that the macro doesnt exist. Do I have to
create a new macro do what I want? also can you explain the
"DoCmd.OpenForm "ParamForm", , , , , acDialog " to me plase?

Kind Regards,
Chris

You placed the code on the Event line rather than in a code window.

Here is how to write code.

In Report Design View, display the report's property sheet.
Click on the Event tab.
On the Open line write:
[Event Procedure]
Then click on the little button with the 3 dots on it taht appears on
that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines, write:

DoCmd.OpenForm "ParamForm", , , , , acDialog

Exit the code window.
Then do the same thing on the Close event (except of course use the
Close code.

Do the same thing on the Form's Command button, writing [Event
Procedure] on the line, click on the little button, and then when the
window opens, write:

Me.Visible = False

as the code.

Save the changes.
Run the report.