Can someone tell me how I can create a Report which is base on a
query where users can select from list controls the field values
for the query. I have a Purchasing Table which contains date,
supplier, part number plus a few other fields. I would like a user
to be able to select from list controls the YEAR, SUPPLIER ID AND
MONTH to create a query which can be used in a report for these
specific parameters. Thanks....
First crate an unbound form with combo or list boxes (combo is
easier) for year, month and supplier ID. Each should have a rowsource
of the distinct values from the appropriate column of the table.
Next create a query that returns all rows of the columns you want in
the report.
Third create the report that shows all the rows, based on the query.
Fourth, On the form, add an Open Report button, using the Wizard.
Fifth, select the button, open the properties box, choose the events
tab, click in the On Click event (it should already say event
procedure) then the elipse (...) button to go into the vba editor.
You will see a line: Dim stDocName As String
add a new line under that Dim stWhereClause As String
and underneath that one lines that say something like
stWhereClause = " year([PurchaseDate]) = " & me.combo1
stWhereClause = stWhereClause & " Month([PurchaseDate]) = " &
me.combo2
stWhereClause = stWhereClause & " [SupplierID] = " & me.combo2
You'll need to change the names of the combo boxes and the fields to
match your particular situation.
Now find the line DoCmd.OpenReport stDocName, acPreview
We must add the filtering to this line:
DoCmd.OpenReport stDocName, acPreview, ,stWhereClause
Save everything and test.
