Report using selection from a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to display a report which is coming from a criteria query. I want to
give the criteria from a form (combo box selection. for example: company
name). I do not know how to build a report which gets a criteria from a form.
 
My standard method is to run code in your form like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboCompany) Then
strWhere = strWhere & " And [CompanyName]=""" & _
Me.cboCompany & """"
End If
DoCmd.OpenReport "rptYourReport", acViewPreview, , strWhere
 
knavlekar said:
I want to display a report which is coming from a criteria query. I want to
give the criteria from a form (combo box selection. for example: company
name). I do not know how to build a report which gets a criteria from a
form.
Click to expand...

Check Help for the DoCmd.OpenReport statement, and look at its
WhereCondition or Filter arguments. That will do what you want.

Alternatively, you could pass the criteria/limiting information in the
OpenArgs argument of DoCmd.OpenReport, then modify the Report's RecordSource
in the Report's Open event. Forms have had OpenArgs for many versions of
Access, but they were new to Reports in Access 2002.

So, if you are using Access 2000 or older, this is not an alternative -- you
would have to pass the information in some other way; a public variable, a
user defined Property of the Report, or make sure it is available in a
Control in an Open (but not necessarily visible) Form.

Larry Linson
Microsoft Access MVP
 
I have performed the task that you mentioned in the form code. What changes
to be made in the report code to get the criteria.

Duane Hookom said:
My standard method is to run code in your form like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboCompany) Then
strWhere = strWhere & " And [CompanyName]=""" & _
Me.cboCompany & """"
End If
DoCmd.OpenReport "rptYourReport", acViewPreview, , strWhere


--
Duane Hookom
MS Access MVP

knavlekar said:
I want to display a report which is coming from a criteria query. I want to
give the criteria from a form (combo box selection. for example: company
name). I do not know how to build a report which gets a criteria from a
form.
Click to expand...
Click to expand...
 
Dear duane,

I have performed that task that mention on the form code. Tell me the
changes to be made on the report code.

Duane Hookom said:
My standard method is to run code in your form like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboCompany) Then
strWhere = strWhere & " And [CompanyName]=""" & _
Me.cboCompany & """"
End If
DoCmd.OpenReport "rptYourReport", acViewPreview, , strWhere


--
Duane Hookom
MS Access MVP

knavlekar said:
I want to display a report which is coming from a criteria query. I want to
give the criteria from a form (combo box selection. for example: company
name). I do not know how to build a report which gets a criteria from a
form.
Click to expand...
Click to expand...
 
I don't think anything has to be change in the report code.

--
Duane Hookom
MS Access MVP

knavlekar said:
I have performed the task that you mentioned in the form code. What changes
to be made in the report code to get the criteria.

Duane Hookom said:
My standard method is to run code in your form like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboCompany) Then
strWhere = strWhere & " And [CompanyName]=""" & _
Me.cboCompany & """"
End If
DoCmd.OpenReport "rptYourReport", acViewPreview, , strWhere


--
Duane Hookom
MS Access MVP

knavlekar said:
I want to display a report which is coming from a criteria query. I want
to
give the criteria from a form (combo box selection. for example:
company
name). I do not know how to build a report which gets a criteria from a
form.
Click to expand...
Click to expand...
Click to expand...
 
Dear Duane,

I would like to clarify my question once again.

I want to open a form at the event of open report. The form has a combo box
and I want to select a value from that box and pass it as a criteria for the
report. Please suggest the changes on the
report side as well as on the form side.

I hope the questions is quite clear now. I want to pop up an form at the
event of opening an report and get the criteria from that pop up form.

Duane Hookom said:
I don't think anything has to be change in the report code.

--
Duane Hookom
MS Access MVP

knavlekar said:
I have performed the task that you mentioned in the form code. What changes
to be made in the report code to get the criteria.

Duane Hookom said:
My standard method is to run code in your form like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboCompany) Then
strWhere = strWhere & " And [CompanyName]=""" & _
Me.cboCompany & """"
End If
DoCmd.OpenReport "rptYourReport", acViewPreview, , strWhere


--
Duane Hookom
MS Access MVP

I want to display a report which is coming from a criteria query. I want
to
give the criteria from a form (combo box selection. for example:
company
name). I do not know how to build a report which gets a criteria from a
form.
Click to expand...
Click to expand...
Click to expand...
 
knavlekar said:
Dear Duane,

I would like to clarify my question once again.

I want to open a form at the event of open report. The form has a combo
box
and I want to select a value from that box and pass it as a criteria for
the
report. Please suggest the changes on the
report side as well as on the form side.

I hope the questions is quite clear now. I want to pop up an form at the
event of opening an report and get the criteria from that pop up form.
Click to expand...

It is more common, and a bit simpler, to use a Form to collect the Criteria
and to Open the Report. The Criteria can be used to generate the
WhereCondition argument of the DoCmd.OpenReport statement.

Is there some compelling reason to do it the other way? Seems if the user
clicks on the Report to Open it, the user would have to have access to the
Database Window, and that is usually avoided in "developed applications."

Larry Linson
Microsoft Access MVO
 
Dear Lerry,

Thanks for the suggestion. You are right user should not get access to the
database window.

I am not worried about that as I am the only user of that database. I was so
eager to get the solution, because I have been trying 'the other way' for a
long time and still not able to do that.

Regards,
Knavlekar
 
If you can't open the report with the simple method that Larry and I have
suggested, then I don't think a more complex solution that uses more code
will be any easier to implement.
 
Back
Top