Solved

Access-SQL

Posted on 2016-08-23
6
107 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 17

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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

689 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