Solved how to use one query starting from 2 different forms (1 Viewer)

[Louisv]

Hi,

I would like to use 1 query to print a report from 2 different forms.
So normally I use the following query’s:
qry_invoice where criteria in design mode is forms!frm_invoices!invoicenr
qry_invoice2 where criteria in design mode is forms!frmdlg_reports!invoicenr

to use 1 query I tried:

dim Inr as string
Inr=forms!frm_invoices!invoicenr (or invnr=forms!frmdlg_reports!invoicenr)
docmd.openreport report!invoice,acviewpreview,, “invoicenr=“ & Inr

to sum different totals on tax I use:

One extra column(): DSum(("[amount]", "qry_invoice", "[invoice]= forms!frm_invoices!invoicenr AND [Tax] =0” )
One extra column(): DSum(("[amount]", "qry_invoice", "[invoice]=forms!frm_invoices!invoicenr AND [Tax] =-1” )

up to this stage all works petfectly, but this still requires 2 queries.

Is it possible to pass the variable “Inr“ to my query “qry_invoice“ in design mode?
to sum different totals on tax:

One extra column(): DSum(("[amount]", "qry_invoice", "[invoice]= '" & Inr & "' AND [Tax] =0” )
One extra column(): DSum(("[amount]", "qry_invoice", "[invoice]= '" & Inr & "' AND [Tax] =-1” )

this does not run, the query asks for Inr, any suggestions are appreciated

kind regards,
Louis

 

[plog]

Don't use the form as criteria in your query. Instead use DoCmd.OpenReport to open and apply a filter to the report itself:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You build the query to show all records (no criteria from the form), the report uses the query and then DoCmd.OpenReport passes criteria to the report to use as a filter when you open the report. That way you can use the same query for both reports and the report itself applies the criteria.

You lost me with the one extra column part. My suggestion is if this is to exist in the query, you make 2 columns--one for each, and then the appropriate report uses the appropriate column. Or just put the DSum on the report itself and not in the query.

 

[Louisv]

Don't use the form as criteria in your query. Instead use DoCmd.OpenReport to open and apply a filter to the report itself:

DoCmd.OpenReport method (Access)

Office VBA reference topic

docs.microsoft.com

You build the query to show all records (no criteria from the form), the report uses the query and then DoCmd.OpenReport passes criteria to the report to use as a filter when you open the report. That way you can use the same query for both reports and the report itself applies the criteria.

You lost me with the one extra column part. My suggestion is if this is to exist in the query, you make 2 columns--one for each, and then the appropriate report uses the appropriate column. Or just put the DSum on the report itself and not in the query.

Thanks Plog,

the query selects just the selected invoice on the form by means of a report

I did use the dsum in the report but then I stil have to use 2 reports because of the different references to 2 different forms in the DSum formula

kind regards,

Louis

 

[Louisv]

I quess my first explanation was not obvious

This is my goal:

I have a report that prints a selected invoice from 2 different forms.
All records of an invoice consist of yes/no box if taxes are obliged
under the sum of the invoice (report) amount I want 2 extra totals.
-the amount where taxes are 0 (not obliged)
-the amount where taxes are -1(obliged)
Therefore I use in a report based on 2 queries by 2 extra columns

query 1 (reference to Form1)
- DSum(("[amount]", "qry_invoice", "[invoice]= forms!frm_form1!invoicenr AND [Tax] =0” )
- DSum(("[amount]", "qry_invoice", "[invoice]=forms!frm_form1!invoicenr AND [Tax] =-1” )

query 2 (reference to Form2)
- DSum(("[amount]", "qry_invoice", "[invoice]= forms!frm_form2!invoicenr AND [Tax] =0” )
- DSum(("[amount]", "qry_invoice", "[invoice]=forms!frm_form2!invoicenr AND [Tax] =-1” )

Question is how can I replace the query reference to a vba variable so that before the command docmd.openreport I can define that variable:

form1
dim Inr as string
Inr=forms!frm_invoices1!invoicenr
docmd.openreport,,, “Inr=“ & Inr

and

form2
dim Inr as string
Inr=forms!frm_invoices2!invoicenr
docmd.openreport,,, “Inr=“ & Inr

For both forms I just like to use 1 report and 1 query, is this possible?

 

[plog]

Question is how can I replace the query reference to a vba variable so that before the command docmd.openreport I can define that variable:

I can't parse that. I don't know what you are asking. What variable? Be more explicit in your question.

For both forms I just like to use 1 report and 1 query, is this possible?

Most likely. It really depends on the differences between the current 2 queries and 2 reports. Can you post a sample database?

 

[MajP]

From form1
docmd.OpenReport "ReportName",acViewPreview,,"Invoicenr = " & me.Invoicenr,,"qryOne"
From form2
docmd.OpenReport "ReportName",acViewPreview,,"invoicenr = " & me.invoicenr,,"qryTwo"

In the report

Private Sub Report_Open(Cancel As Integer)
  If Nz(Me.OpenArgs & "") <> "" Then
    Me.RecordSource = Me.OpenArgs
  End If
End Sub

You are overthinking this one. Just change the recordsource to the correct query when the report opens.

 

[Louisv]

From form1
docmd.OpenReport "ReportName",acViewPreview,,"Invoicenr = " & me.Invoicenr,,"qryOne"
From form2
docmd.OpenReport "ReportName",acViewPreview,,"invoicenr = " & me.invoicenr,,"qryTwo"

In the report

Private Sub Report_Open(Cancel As Integer)
  If Nz(Me.OpenArgs & "") <> "" Then
    Me.RecordSource = Me.OpenArgs
  End If
End Sub

You are overthinking this one. Just change the recordsource to the correct query when the report opens.

Thanks, but it seems that this still needs 2 queries, with other form references, for the same report

 

[Gasman]

Remove the form references then. Use tempvars in their place
Set the tempvars before even trying to use the report?

 

[Louisv]

Remove the form references then. Use tempvars in their place
Set the tempvars before even trying to use the report?

Never heard of tempvars but I am gone learn about them, thanks

 

[Louisv]

Thanks for your replies, finally found why I could not find a solution:

A query cannot directly reference a VBA variable. It can, however, call a VBA function that returns the value stored in the VBA variable.

solutions:
- use vba function
- use tempvars

 

[MajP]

I would simplify this

Do an aggregate query based on qry_Invoice group by line number, sum the amount, where tax = 0
Call that qrySumNoTax
That should have the total amount by each invoice

Do the same where tax = -1
call that qrySumWithTax

Join your reports query to both qrySumNoTax and qrySumTax by invoice number
pull in the two summed amounts. Alias one as TaxableSum and the other as NonTaxableSum

now simply open the report based on invoice number.

 

[Louisv]

I would simplify this

Do an aggregate query based on qry_Invoice group by line number, sum the amount, where tax = 0
Call that qrySumNoTax
That should have the total amount by each invoice

Do the same where tax = -1
call that qrySumWithTax

Join your reports query to both qrySumNoTax and qrySumTax by invoice number
pull in the two summed amounts. Alias one as TaxableSum and the other as NonTaxableSum

now simply open the report based on invoice number.

Thanks MajP!

I have tried this solution and it works, I have linked the main invoice query with 2 total Tax queries.

However it requires now 3 queries

Improvement: I can still start the same main invoice Query from all forms with the openreport command !

 

[MajP]

However it requires now 3 queries

What is the big concern about the number of queries? Who cares. In truth that solution could be written as one big query, but it is a lot easier to do it in parts. Stored queries require hardly any resources. If you name them efficiently and group them they can be easily managed. Your original plan was to use tempvars and vba code to save on a query. So you are writing an inefficient and hard to trace solution to save on 1 query. Makes no sense.

 

[Louisv]

efficiency comes first

 

[Pat Hartman]

If efficiency comes first, you need to understand that domain functions have no place in a query or code loop. The solution Maj offered is far more efficient. Each domain function runs a separate query and that involves overhead. You were using 4 domain functions in the query so each row the query returned, let's say 1000 rows, needed to run 4 separate queries. That comes to 4,000 queries for not a lot of data but there is a lot of overhead with enstanciating the query because the domain function query is not a stored querydef. Therefore, each time it runs, Access needs to calculate an execution plan to determine how to retrieve the requested data.