Access-SQL

Hello All,

I'm printing a few invoices at a time with Header and detail info.
With access it works fine because the Link Child fields and Link Master fields will only print the details for that invoice no.
But with SQL and pass thru queries the Link Child and Master fields don't work. So it prints for each Invoice all the Details from other Invoices.
How can we work around this problem?
Thanks in advance.
LVL 1
frimyAsked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
Doesn't seem that there is a way.
Tried wrapping the passthrough in another, regular query -- it doesn't work.
Regardless of what is here
https://social.msdn.microsoft.com/Forums/office/en-US/e2a246cd-b26f-4864-b037-50f6b49e553c/passthrough-query-and-access-2010-report?forum=accessdev
a temp table seems to be required.

On a side note, you are going to close out and assign points on the Q and your previous two right?
Right?
0
 
Kelvin SparksCommented:
You need to define the Invoice No in both the header and details queries. Generally, I'd set a parameter being the Invoice No, and pass it to both.


Kelvin
0
 
Nick67Commented:
But with SQL and pass thru queries the Link Child and Master fields don't work. So it prints for each Invoice all the Details from other Invoices.
If you change the recordsource and don't have the matching fields, Access won't complain, and just treats the sub-objects as unbound.

For example.  
The main report recordsource is "Select ClientID, theName from tblClients"
The subreport recordsource is "Select ClientID, PhoneNum from tblPhoneNums"
I build a report that uses ClientID as the Masetr/Child field
I then change the recordsources to sprocs
The main sproc returns ClientID and theName from tblClients
The sub sproc returns just PhoneNum from tblPhoneNums

Oops. Doesn't work.  It errors. You're right

Remove the master/Child and I'll get all the phone numbers under every client.
You've found an old limitation
https://support.microsoft.com/en-us/kb/112746
You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport.

So, did you cheat up a batch to make it work? And not cheat sucessfully?
As Kelvin said, in the case of a passthrough for a subreport, you have to tweak the subreport passthrough to use a parameter from the main query to fetch only the desired records.
That'll mean dynamically re-writing the SQL of the passthrough for each time the subreport renders.
I assume the subreport is in the Detail section?

You then have to, in the Detail_Format event put code like
dim qdf as querydef
set qdf = CurrentDb.QueryDefs("TheNameOfTheSubReportPasthrough")
qdf.SQL = "Some nice new SQL that passes in a valid T-SQL command that will get records only for this detail section")
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
John TsioumprisSoftware & Systems EngineerCommented:
In such case i find it is easier to create a temporary table(s) that holds as the source for the subreport(s).
So you create a temp table(s) that has the same structure as the pass through(s) query...
On the code that you use to call the report first you clear the temp table...then you run an append query with the pass through as the source...
0
 
frimyAuthor Commented:
Thanks to you all
I already did a temp table.
I just thought there is a easier way without a temp table.
Have a nice day everybody!
0
 
frimyAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.