We help IT Professionals succeed at work.

Run Access 2007 report based on query using a dialog box to supply the criteria

Scamquist
Scamquist asked
on
5,274 Views
Last Modified: 2013-11-28
I have a report COMMUNICATION BY LOCATION that uses the query CommLine, which is based on the dialog box SelComTypeLoc.  
The criteria for the query is [Forms]![SelComTypeLoc]![cLoc]

When I open the dialog box   SelComTypeLoc and enter my value (cLoc) into a drop down box, the query executed properly.

I am trying to open the report and run it based on the values set in the dialog box SelComTypeLoc to run the query CommLine.

The report record source cannot be the form  SelComTypeLoc.  

How can I have the report run the dialog box to select the query values?
Comment
Watch Question

Commented:
Are you sure you want to open the form from the report, or open the report from the form?

I would change the Recordsource property of the form during the Open event using VBA code.  Something like:

Report.Recordsource = "SELECT * FROM MyTable WHERE Criteria = ' & Forms]![SelComTypeLoc]![cLoc].

I'm sure you'll need to tweak that a little...

Author

Commented:
If I remove the paramaters from the query, the report will run with CommLine as the record source in the report.  I know the dialog box works because if I open the form SelComTypeLoc first, enter my value and click OK the proper value is returned on the query.  I am trying to open the report and run the query based on the dialog box in one smooth step.  

One other piece,  I am using a command button on another form to kick off the report...if that makes any difference.
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
OUTSTANDING!
Thank you for your help,  The attached file saved the day.  I found the link you provided, which is how I got as far as I did.  However, the code they use is to have the OK button run the query, not open the report.  The OK button on my dialog box was opening the query, not the report.  I made the change and it worked like a champ.     Thank you again.again.                                          
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.