Solved

Access-SQL

Posted on 2016-08-23
6
74 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

770 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