Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Access-SQL

Posted on 2016-08-23
6
Medium Priority
?
145 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 19

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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

609 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