Use the same report with different query's

[chuckcoleman]

Hi, I want to use the same report with different. If the user is on Form "A" and they click on a button it will use:

Private Sub Command105_Click()
Dim WhichQuery As String
WhichQuery = "Invoice Query-3-Bulk-Reinvoice"
DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, WhichQuery
End Sub

If they click on a different button, the value for WhichQuery would be different.

I assume you leave in the report on the data tab the Record Source would be blank. It is my understanding that the Filter parameter in OpenReport can be a query. Therefore I'm trying to pass the value of WhichQuery to the report so it uses that query. When I run the code I get a "The action or method is invalid because the form or report isn't bound to a table or query."

What am I doing wrong?

Thanks,

Chuck

 

[Ranman256]

you can just filter the report, not change the query. The query would show all data, but the openRpt would filter:
DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, ,"[State]='" & me.cboState & "'"

if you DO want to swap queries (don't see why) youd use code to copy the source query to the target rpt query:
docmd.CopyObject ,sTargQry, acQuery, sSrcQry
DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview

 

[bob fitz]

Does it work if you just use:

Private Sub Command105_Click()
  DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, "Invoice Query-3-Bulk-Reinvoice"
End Sub

 

[chuckcoleman]

Ranman256, thanks.

The two query's I want to use with the same report are exactly the same, except two fields in the query have different Criteria. The criteria for query #1 is based on which form the user clicked on, i.e. a WorkNbr on form A and in query #2 it is based on form B.

Therefore, maybe I can use the same query for the report but how do I pass which form I'm using to the criteria on two different fields in the query?

 

[chuckcoleman]

Does it work if you just use:

Private Sub Command105_Click()
  DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, "Invoice Query-3-Bulk-Reinvoice"
End Sub

Hi Bob, I get the same error message.

 

[bob fitz]

Try:

Private Sub Command105_Click()
  DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, "[Invoice Query-3-Bulk-Reinvoice]"
End Sub

 

[chuckcoleman]

Try:

Private Sub Command105_Click()
  DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, "[Invoice Query-3-Bulk-Reinvoice]"
End Sub

Nope, same error. The report doesn't have any records bound to it.

 

[Ranman256]

the form shown, the user will fill a control,
then the button will open the report, using the controls on the form
docmd.openreport…..,"[State]='" & me.cboState & "'"
or
docmd.openreport…..,"[LastName]='" & me.txtBoxName & "'"

 

[bob fitz]

Does the query "[Invoice Query-3-Bulk-Reinvoice] show any records if opened manually.

 

[chuckcoleman]

Does the query "[Invoice Query-3-Bulk-Reinvoice] show any records if opened manually.

Absolutely, yes.

 

[bob fitz]

You could do this another way:

Open the report and use the query name as the "OpenArgs" parameter

    DoCmd.OpenReport "tblT1", acViewPreview, , , , "Invoice Query-3-Bulk-Reinvoice "

and then put the following code the the reports On Open event:

If Not IsNull(Me.OpenArgs) Then
    Me.RecordSource = Me.OpenArgs
End If

 

[Gasman]

I would and have done using TempVars instead of hardcoding form values.?
Each form sets the TempVar then uses the query?

 

[theDBguy]

Hi, I want to use the same report with different. If the user is on Form "A" and they click on a button it will use:

Private Sub Command105_Click()
Dim WhichQuery As String
WhichQuery = "Invoice Query-3-Bulk-Reinvoice"
DoCmd.OpenReport "Invoice Report-BulkTest", acViewPreview, WhichQuery
End Sub

If they click on a different button, the value for WhichQuery would be different.

I assume you leave in the report on the data tab the Record Source would be blank. It is my understanding that the Filter parameter in OpenReport can be a query. Therefore I'm trying to pass the value of WhichQuery to the report so it uses that query. When I run the code I get a "The action or method is invalid because the form or report isn't bound to a table or query."

What am I doing wrong?

Thanks,

Chuck

Hi Chuck. Pardon me for jumping in, but I think you had the wrong assumption above (highlighted in red). Try binding the report to the same table you used in your queries or a query without any criteria and then try Bob's suggestion again of using your two queries as a Filter argument in your OpenReport method. Cheers!

 

[chuckcoleman]

With all your help, I solved it. As in many things, there's more than one way to solve the problem. I used TempVars in the two forms where I set TempVars = the forms path, i.e. TempVars!ClientX = [Forms]![Re-invoice Form]![ClientNbrX].Value (That is for one of the forms. The other form has a different path pointing to that form.) I then used in the Query as Criteria TempVars!ClientX. It works great!; the same Query can be used in one report but it will use data based on the form I run the report from.

Thank you all!

 

[theDBguy]

With all your help, I solved it. As in many things, there's more than one way to solve the problem. I used TempVars in the two forms where I set TempVars = the forms path, i.e. TempVars!ClientX = [Forms]![Re-invoice Form]![ClientNbrX].Value (That is for one of the forms. The other form has a different path pointing to that form.) I then used in the Query as Criteria TempVars!ClientX. It works great!; the same Query can be used in one report but it will use data based on the form I run the report from.

Thank you all!

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[zeroaccess]

With all your help, I solved it. As in many things, there's more than one way to solve the problem. I used TempVars in the two forms where I set TempVars = the forms path, i.e. TempVars!ClientX = [Forms]![Re-invoice Form]![ClientNbrX].Value (That is for one of the forms. The other form has a different path pointing to that form.) I then used in the Query as Criteria TempVars!ClientX. It works great!; the same Query can be used in one report but it will use data based on the form I run the report from.

Thank you all!

I use this approach or use a WHERE condition that is different depending on the button clicked.