Solved

Access-SQL

Posted on 2016-08-23
6
66 Views
Last Modified: 2016-08-30
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.
0
Comment
Question by:frimy
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41767881
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
 
LVL 26

Expert Comment

by:Nick67
ID: 41767918
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
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41769780
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:frimy
ID: 41770362
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 41770611
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
 

Author Closing Comment

by:frimy
ID: 41776772
Thank you
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now