Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-22-2019, 03:06 AM   #1
Gingerale75
Newly Registered User
 
Join Date: Oct 2019
Posts: 5
Thanks: 8
Thanked 0 Times in 0 Posts
Gingerale75 is on a distinguished road
Open report from report with double click event

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


Last edited by isladogs; 10-22-2019 at 03:35 AM. Reason: Added code tags to improve readability
Gingerale75 is offline   Reply With Quote
Old 10-22-2019, 03:36 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,288
Thanks: 115
Thanked 3,089 Times in 2,807 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Open report from report with double click event

Your post was moderated, probably due to the code section.
I've approved it and added code tags (# on toolbar)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
Gingerale75 (10-22-2019)
Old 10-22-2019, 03:48 AM   #3
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Open report from report with double click event

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

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

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Gingerale75 (10-22-2019)
Old 10-22-2019, 05:55 AM   #4
Gingerale75
Newly Registered User
 
Join Date: Oct 2019
Posts: 5
Thanks: 8
Thanked 0 Times in 0 Posts
Gingerale75 is on a distinguished road
Re: Open report from report with double click event

Quote:
Originally Posted by arnelgp View Post
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?

Code:
 
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:
Code:
 
|---------------------------------------------------|
|  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.
Gingerale75 is offline   Reply With Quote
Old 10-22-2019, 07:20 AM   #5
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,383
Thanks: 13
Thanked 4,122 Times in 4,054 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Open report from report with double click event

Note the delimiters required for a text data type:

http://www.baldyweb.com/wherecondition.htm
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-22-2019, 07:32 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Open report from report with double click event

check the textbox Name, then put it in your dblclick event:
Code:
Private Sub CustID_DblClick(Cancel As Integer)
    DoCmd.OpenReport ReportName:="rpt_Customer", View:=acViewReport, WhereCondition:="CustID='" & [CustID] & "'"
End Sub

__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 10-22-2019 at 08:35 AM.
arnelgp is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Double Click event to open to specific record T. McConnell Reports 3 06-30-2019 11:42 AM
Double Click event not triggering After Update event George-Bowyer Modules & VBA 7 03-14-2019 11:50 PM
Opening a Form based off of a Double Click event on a Report Okibi07 Reports 18 08-15-2013 02:51 PM
Double Click Event to Open Form showing Specific Subform samjh Forms 0 01-30-2013 06:07 AM
How to open a form from a double-click event M0E-lnx Forms 4 09-09-2008 02:25 PM




All times are GMT -8. The time now is 02:16 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World