We help IT Professionals succeed at work.

MS Access 2010 Form Button, Query, Report

Dear Experts,

  I created a Form, a Query and a Report in MS Access 2010.  How do I tight these 3 components together, so when a button is clicked on the Form, the Report will show up and displaying the records according the fields in the Form.

Form:

the form has 3 text fields and a command button.
3 text fields:  passing Date, ModelName, Color parameters.

1 command  button: when button is clicked, the report will show up and displaying the records according to the fields in the 3 text fields.

Query:
   
a Query statement showing all records regarding the 3 parameters that are passed from the form.
this query's WHERE clause refers to the Form and the 3 parameters.
When click Run, this query is running fine by itself and displaying records.

Report:

a report is made and set the control source to the name of the above query.



My Question

The command button of the Form has an onClick event tight to a Macro with an Action of "SetValue": item = [visible]   expression = no

The Report will display records without problems if I do the following:

1. Enter the values of the 3 parameters on the Form.
2. Click the command button.  -> then this Form goes away.
3. Navigate to Queries in MS Access. Click on the Query  -> then records shows in grid view.
4. Navigate to Reports in MS Access. Click on the Report -> the records of the Query show in the Report.


How do I tight together the command button on the Form, the Query, and the Report together, so when I click on the command button on the form , the Report will open and display the records according to the 3 text fields on the Form?

Thank you!

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Use VBA instead of a Macro - it is easier to follow and to maintain in the long run, even if there is a learning curve.

The VBA to open the report from a command button on a form would be something like this:

Private Sub cmdMyButton_Click()
     dim strSQL as string
     strSQL = "[passing Date] = #" & me.[Passing Date] & ", [ModelName] = '" & me.[ModelName] & "', [Color parameters] = '" & me.[Color Parameters] & "'"
     Docmd.openReport "MyReport",,,strSQL
End Sub

Open in new window



You'd have to replace fields in the above code with your own actual field names.
CERTIFIED EXPERT
Top Expert 2016
Commented:
try this
private sub cmdOpenReport_click()
dim strFilter

strFilter="[date field name]=#" & me.datefieldcontrol &"# and [textFieldName]='" & me.textFieldControl & "' and [number field name]= " & me.numberfieldcontrol

docmd.openreport "yourReportName",acviewpreview,, strFilter

end sub

Open in new window

NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
Sounds like that macro action is hiding the form.

If you have everything set up properly, ie the parameters, then you should just be able to add an open report action to the button's onclick macro.

However, I agree with mbizup - using code is easier to follow and gives you more control.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Looks like I had a typo in my code.

Corrected:

Private Sub cmdMyButton_Click()
     dim strSQL as string
     strSQL = "[passing Date] = #" & me.[Passing Date] & "#, [ModelName] = '" & me.[ModelName] & "', [Color parameters] = '" & me.[Color Parameters] & "'"
     Docmd.openReport "MyReport",,,strSQL  
End Sub 

Open in new window


Also, just an FYI, the syntax I used sends your report to the printer:
     Docmd.openReport "MyReport",,,strSQL

The syntax capricorn1 used will show it in print preview:
     Docmd.openReport "MyReport",acviewpreview,,strSQL

Author

Commented:
nice! mbizup, there's still a little syntax error, but i got it square away.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help out!

Just FYI, an option in closing questions when you correct/fix solutions like that is to post the final code, and include your own answer among the accepted solutions (authors don't receive any points for accepting their own responses, it just a way of sharing the code you actually used).
CERTIFIED EXPERT
Top Expert 2016

Commented:
what was the syntax error ?  the comma (,) ?

Explore More ContentExplore courses, solutions, and other research materials related to this topic.