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:
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.
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.
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.