Building macro to run Query(w/dropdown parameters) then report

  • Thread starter Thread starter christiekp
  • Start date Start date
C

christiekp

I have a query with parameters from which I'd like to run a report. I can do
so with regular parameter prompts but would like to use a drop down box. I
have read the instructions in www. fontstuff.com/access/ about how to create
the combo box for the parameter, however, I'm not sure how to get the macro
to wait for the query to run before the report. It HAD been a select query,
but I changed it to a make a table query but it stil didn't work.

Any ideas?
 
Can't you just remove the parameters altogether and allow the combo box (and
maybe a text box or two) to filter your report?
Evi
 
I have a query with parameters from which I'd like to run a report. I can do
so with regular parameter prompts but would like to use a drop down box. I
have read the instructions in www. fontstuff.com/access/ about how to create
the combo box for the parameter, however, I'm not sure how to get the macro
to wait for the query to run before the report. It HAD been a select query,
but I changed it to a make a table query but it stil didn't work.

Any ideas?

Let's assume you wish to filter the report so that only records
pertaining to a particular company are shown. Change the field names
as needed, but the process is the same.

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

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

(Note: To write the code, in Form Design View select the command
button. Display the button's property sheet.
Click on the Event tab.
On the On Click line, write:

[Event Procedure]

Click on the little button with the 3 dots that appears on that line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those lines, write the following code.)

Me.Visible = False

Close the Code window.

Name this form 'ParamForm'.

In the Query that is the Report's Record Source [CompanyID] field
criteria line, write:

forms!ParamForm!FindCompany

Next, code the Report's Open event:
(Using the same method as described above)

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 Company.
Click the command button and then report will run.
When the report closes, it will close the form.
 
Let's assume you wish to filter the report so that only records
pertaining to a particular company are shown. Change the field names
as needed, but the process is the same.
[snip]

I'm not the original poster, but I just want to say thank you - that's
given me exactly the information I needed for a similar issue.

John
 
Road Block

Fredg,

I'm working on a project tracking database for my department and in trying to find a way to do the drop down in a parameter query, came across your instructions. They worked great for one of my two reports, but I cannot seem to make it work for the other.

My theory is that it won't work due to the fact my drop down form is pulling from the same database as the report's fields. The report that works uses a table for the drop down form (a list of my company's departments) separate from the two tables used for the report information. In the report that will not work, the drop down form pulls from within the main table used for the report (a list of project names). When I run the report, I am prompted to pick the project name from the drop down, but the report itself always comes back blank. Any suggestions as to a fix? I did attempt to have a query run and pull all the project names into a table and then use that table for the drop down form, but I ended up with the same results.

Thank you for your time.

~Shannon
 
Back
Top