Open report from report with double click event

[Gingerale75]

Hello All,

I faced a problem where I couldn't figure out the solution.
I'm not expert on Access but not a complete beginner either.

There are two queries (Cust_1 & Cust_2), which collects customer details from two ODBC tables (one for each query). The ODBC tables include a customer ID field which called 'CustNr' in one of the ODBC table but called 'CustomerID' in the other.

The result fields of the queries called CustID, ForeName, SurName.

There is a union query (Customers) to merge the results. The union query is there because it's unknown that in which ODBC table will be match for the given CustID, can be only in one or in both.

There is a report (rpt_Customer) which should show the customer details from the ODBC tables (using the union query) based on the CustID parameter.

There is another report called rpt_Product which shows a particular product and its CustID from a local table.

What I need to do:
- Able to run the rpt_Customer report with a parameter entry window for the CustID, but I want to avoid to get the pop up window twice for the two queries within the union query.
- When the rpt_Product report is shown first, then I want to double click on the CustID, and it should run the rpt_Customer with the CustID passed to it (from the value of the field I double clicked on) as a parameter without showing the parameter request window for the Cust_1 & 2 queries.

I tried to do the tasks in various ways:
1.
Set parameters on the separate queries (Cust_1 & 2), which works OK if the these queries run separately, but as soon the union query is used for the report, I can't manage to request the parameter only once, it always ask twice.
For the double click thing it works only if I use only one of the queries (Cust_1 or 2) with a Macro Builder event because in this case the builder can recognise that the Cust_1 query has a parameter. But as soon I use the union query in the builder it can't recognise that the sub-queries has parameters hence I can't pass the text field value to them.

2.
No parameters defined on the separate queries but define parameters in the union query:

SELECT * FROM Cust_1
WHERE Cust_1.CustNr = [CustID]
SELECT * FROM Cust_2
WHERE Cust_1.CustomerID = [CustID]

It gets even worst because it pops up three parameter entry window, asking for values for CustNr, CustID, CustomerID.
For the double click thing it does the same thing, asking the parameter three times, because in the event builder it can't see the parameter definition in the SQL, only see the fields in the sub-queries.

3.
There might be a solution that none of the queries (Cust_1, Cust_2 and the union Customers) has a parameter defined and a WHERE statement defined in the rpt_Products for double click event (WHERE Customers.CustID = rpt_Products.CustID or something like that where the rpt_Products.CustID is the value of the text field I double clicked and it passed to the union query) but then if the rpt_Customer report run separately, then it will not request the CustID and will show all records from both ODBC tables.

I hope my description was clear.
Looking forward to see your advise.

 

[isladogs]

Your post was moderated, probably due to the code section.
I've approved it and added code tags (# on toolbar)

 

[arnelgp]

remove all Parameters from your Queries.
open the report with Where Clause:

DoCmd.OpenReport ReportName:="theReport", View:=acViewPreview, WhereCondition:= "CustID=" & [CustID]

 

[Gingerale75]

remove all Parameters from your Queries.
open the report with Where Clause:

DoCmd.OpenReport ReportName:="theReport", View:=acViewPreview, WhereCondition:= "CustID=" & [CustID]

Thanks, however I'm not sure in which report should I copy this line.

Should it be onto rpt_Products --> CustID properties --> double click event --> Event procedure?

Private Sub CustID_DblClick(Cancel As Integer)
    DoCmd.OpenReport ReportName:="rpt_Customer", View:=acViewReport, WhereCondition:="CustID=" & [CustID]
End Sub

I tried this as this seemed the most logical, but when I double click on the CustID field on the rpt_Products then it still asks for a parameter and the parameter entry window look a bit weird because, let's say that the value of the CustID which I'm double clicking on it is "B326", then the parameter window is showing this as a label:

|---------------------------------------------------|
|  B326                                             |
|  [ here is the text field I need to type value ]  |
|---------------------------------------------------|

If I type here 'B326' then the rpt_Customer report opens OK showing the details of customer B326 (checking both ODBC tables).
But why is it asks for the parameter? I guess somehow it doesn't pass the value of the CustID text field (B326) directly onto the WhereCondition:="CustID=" & [CustID] statement.

Also if would I use the method above and I need to run the rpt_Customer report separately, not via the other report then it will show all records because it doesn't have parameter defined.

Or did I try to use your suggested code in the wrong place?
I had a thought on an 'Open event' when the rpt_Customer opened/run but I wasn't sure where to copy your code in this case.

 

[pbaldy]

Note the delimiters required for a text data type:

http://www.baldyweb.com/wherecondition.htm

 

[arnelgp]

check the textbox Name, then put it in your dblclick event:

Private Sub CustID_DblClick(Cancel As Integer)
    DoCmd.OpenReport ReportName:="rpt_Customer", View:=acViewReport, WhereCondition:="CustID='" & [CustID] & "'"
End Sub