Passing Parameters Values to Reports 1

[Bensta]

I have a form, from which users select a location (Combo Box/Value List), Equipment Type (Combo Box/Value List), and a specific report from a list box. The choices should be optional. That is, the user does not have to select all the values to run a specific report. Is there any example that uses code that accomplish this task?? I would truly appreciate any help.
Thank you in advance.
Bensta

 

[dhookom]

This depends on how you apply the criteria. Does your query contain references to the controls or are you using a where clause in the DoCmd.OpenReport command?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bensta]

I have pointed criterias in the query to the controls on the forms. But all of the criteria had to be selected for the report to run. I don't want the user to be forced to select all the criterias. I know you could build your string from code and pass the parameters selected to a specific report. But again I don't know how to do that.
Thank you

 

[dhookom]

I usually build the where string in code. The On Click code of a button to open a report looks like:
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.cboLocation) Then
strWHere = strWhere & " AND [Location] = """ & Me.cboLocation & """ "
End If
If Not IsNull(Me.cboEquipType) Then
strWhere = strWhere & " AND [EquipType] = """ & me.cboEquipType & """ "
End If
DoCmd.OpenReport "rptMyReport", acPreview, , strWhere

If you want to continue using the control references in the query, you can use:
Nz(Forms!frmYourForm!cboLocation,[Location])


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bensta]

Thank you so much for your help. I appreciat it a lot.
I have one more question to ask. In addition to selecting the creteria from the form controls, I want the user to be able to select a report from a list box that holds a list a of multiple reports after selecting the criterias. That is, in the DoCmd.OpenReport "rptMyReport", acPreview, , strWhere
"rptMyReport" becomes also a vaiable. How do I go about doing that? I hope I was clear with my question
Thank you

 

[dhookom]

Next time, please provide the name of your list box.
Try:
[blue]

  Dim strWhere As String
  Dim strRptName as String
  strRptName = Me.lboReports
   strWhere = "1=1 "
   If Not IsNull(Me.cboLocation) Then
      strWHere = strWhere & " AND [Location] = """ & Me.cboLocation & """ "
   End If
   If Not IsNull(Me.cboEquipType) Then
      strWhere = strWhere & " AND [EquipType] = """ & me.cboEquipType & """ "
   End If
   DoCmd.OpenReport strRptName, acPreview, , strWhere

[/blue]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bensta]

Thank you so much. It worked beautifully. The list box is named RptName
What does strWhere = "1=1 " mean and do?
Thank you so much. I give you a star

 

[dhookom]

The "1=1 " is used only because I am lazy. If I didn't use this, I would have to check strWhere to determine if I need to use the " AND " or not when adding a new condition to strWhere. My code is written to always include the " AND " because strWhere always has at least one condition (1=1).

You could probably use "True " or "2=2 " or "-1 " in place of "1=1 " and it wouldn't make any difference.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Bensta]

Do you have a similar example but it uses the and/or operators instead in the stWhere clause.
If I understand you correctly, 1=1 is kind of treated as and AND because 1=1 is always true but 1=2 is always(most the times )false??
If you omit the stWhere= "1=1", how would you go about checking the stWhere to see if it needs and AND or an OR?
Thank you for bearing with me
Bensta

 

[dhookom]

You would never use "1=2 " since your report would not return any records. If you wanted to use " OR " vs " AND ", you would need to provide an option group or list box or check box to get this input from the user. Combining ORs and ANDs becomes quite complex since you may need to add ()s on specific groups of conditions. I have found that this is rarely required.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.