INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How To

Use a form and query to send criteria to a report by randysmid
Posted: 4 Nov 02 (Edited 23 Oct 03)

I like to use a form where the user can select various criteria for a report.  The report recordsource will be a query, and my form will pass the selection info to it in the form of criteria.  

First, set up a form with all the fields desired for the user to select his/her criteria.  I usually use comboboxes or listboxes based on a table or query.  BUT, to allow wildcard lookups, I generally add a new item to the table.  For instance, in a table of U.S. states, I added a state code of "%" (without the quote marks), and for the description I used "<all states>".

Second, the form should have a command button that allows the user to run the report.  It is at this time you can check to make sure that the user has selected all the required criteria.  Of course, the code would include the call to run the report, e.g., "DoCmd.OpenReport "rptStates", acViewPreview

Third, I set up a query to gather the information needed. For each of the fields that appears on the selection form, I set the criteria box to a specific field on the form.  For instance, let's say that I let the user select the year from a table of accounting records.  Here is how the criteria line will look for the field called fldYear:
=[Forms]![frmInputCriteria]![txt_Year]
(By the way, this same line can appear as the Control Source for a text box on the header area of a report, thereby allowing a user of the report to know what criteria was used to select that particular set of data)

Fourth, set up a report, and base the report on the query you have just created.  If you want, you may print the selection criteria in the header as just explained.

So, here is what happens:
1) The user enters the criteria into the form, and clicks the cmdReport button.
2) The code will check to make sure all necessary criteria has been selected (instead of having nulls).
3) The code will then start the report, which will turn around and run the query.
4) The query will check all the criteria, and build the result set, which is then returned back to the report.
5) The report is built, and then displayed to the user.

Feedback, comments?  
Randy Smith, MCP
rsmith@cta.org    


Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close