Windows XP 
Access 2007
To do what you are asking requires skill in MS Access VBA code. Essentially you must know VBA and SQL to created the means to call up the report based upon criterion. Let me go into as much detail as I can.
Let us say you have a form called "frmthisForm" with a field called "ThisField" that you want to place criterion in for the report. You would write code into the On Click event. (If you don't know how to do this then you should really call an instructor to explain how because this is not child's play. You can write me back and I can answer your questions in detail if you want. You rename the button to a meaningful name, like "btnopenReport." Then, in design view still, right click the button and choose "Build Event" from the pop-up menu. Then choose "Code Builder." In the code you will see the on-click sub procedure started for you. in the body of this procedure you'd make a reference to the value the user put in the "ThisField" field. You would then make a statement with SQL syntax
dim x
x = me!thisfiled
Now depending on the nature of x, whether it is a number, a text or a date, you'd have to write the sql criterion statement to pass to the report. it would look something like this if the value were a number:
"thisfield = " & x
If it were a text you'd write:
"Thisfield = '" & x & "'" where it x is enclosed with apostophes.
If this were a date field the field would be enclosed in pound signs instead of apostrophes.
This is the criterion statement so you would do the following:
DoCmd.openreport "rptThisReport", "ThisField = " & x
The report would open up to the criterion specified on your form for the field "Thisfield" where "Thisfield" is also in the recordset that the report is based on in its Recordsource property.
I hope this helps.
Reply With Quote
