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!
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!
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:
You'd have to replace fields in the above code with your own actual field names.
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
You'd have to replace fields in the above code with your own actual field names.
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
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.
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.
Looks like I had a typo in my code.
Corrected:
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,,
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
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,,
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).
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).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial