AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Thursday, August 11, 2005

Pass-through queries in Access subforms and subreports

Recently, while working on Access application as s front-end to SQL Server I came into a problem that you can not bind subreport (and subform) to pass-through query, if subreport linked to main report with Link Master/Child properties. Error message I get is: "You can't use a pass-through query or a non-fixed column crosstab query as a record source for a subform or subreport". Microsoft KB article says that you can bind subreport directly to linked table and then it will work. Yes, it will work, but imagine what happened for a large table? Or if you need to join 2 tables for this subreport? I think better approach is to use a local temporary tables - so just copy your filtered pass-through query to some local table and then use it as a report source.

16 Comments:

Anonymous Anonymous said...

Is this bug present in Access XP or only Access 2003?

What if we load a DAO.Recordset and set Subform.Form.Recordset property, will it work?

2:16 AM  
Blogger Alex Dybenko said...

I think this is a feature of Access, also in 2003.

As for Recordset property - for SQL Server database you can set only ADO recordset. See Recordset Property in Access help

10:47 AM  
Anonymous Anonymous said...

How do you create a "temporary local table" from a pass-through query?

Thanks!

7:17 PM  
Blogger Alex Dybenko said...

Just run a Select Into (or make-table) query, based on pass-through query

10:36 AM  
Anonymous Anonymous said...

Hi Alex, what if I create the local table (instead of binding subreport directly to linked table), but I am using some filters (dialog box) in order to do some queries to the local table. The problem will rise when the subform is based on the local table, meanwhile the filters are trying to run a Select Into (or make-table) query, based on pass-through query. What can I do to visualize the new "temporary local table" in a subform, every time I use the filters in the pass-through query ?

Thanks
HA

10:27 PM  
Blogger Alex Dybenko said...

Hi,
If i correctly understand - in report opne event you have to prepare your local tables, using select into, and then your report will run on filtered data

8:45 AM  
Anonymous Anonymous said...

Correct Alex!! But how can I show the report in a subform (based on the local table), and make the subform to refresh with the new information given every time I select the pass-through query(with the filters). I mean how can I make the subform (based on the local table) to stand by meanwhile I refill the local table with new info (from the pass-through query) and then having again at disposal the subform with the information of the local table refreshed.

Thanks,
HA

9:15 PM  
Anonymous Anonymous said...

Correct Alex!! But how can I show the report in a subform (based on the local table), and make the subform to refresh with the new information given every time I select the pass-through query(with the filters). I mean how can I make the subform (based on the local table) to stand by meanwhile I refill the local table with new info (from the pass-through query) and then having again at disposal the subform with the information of the local table refreshed.

Thanks,
HA

9:26 PM  
Blogger Alex Dybenko said...

hi,
in order to refresh subform with new data - you need to refill local table and then reopen report or requery form:
me.recordsetsource=me.recordsetsource

11:54 AM  
Anonymous Anonymous said...

WOW!!!! It worked Alex !! I used the recordsource using two tables A and B in order to change the form1´s recordsource meanwhile I refill table A based on the passthrough query also with filters.
You made my day
Thanks
But only a question:
Forms!form1.RecordSource = "tableA"
only works when form1 is not in or pasted in my "baseForm or "generalForm", how can can I make form1 change it´s record source, being in my Baseform" and not being separated from my baseform" or generalform where I have all my filters, buttons, etc.

HA

9:27 PM  
Blogger Alex Dybenko said...

hi,
in this case you have to use:
Forms!form1!MySubform.From.RecordSource = "tableA"

6:54 PM  
Anonymous Anonymous said...

Thanks Alex !! Took a little time but I've finished.

Thanks a lot !

HA

1:43 AM  
Anonymous Anonymous said...

Hello Alex, I need to create in access a command button that can obtain automatic downloads from the internet, The download is from an specific web page, what can I do?

Thanks

1:48 AM  
Blogger Alex Dybenko said...

Hi,
you can look at this link for a sample code:
http://www.mvps.org/access/modules/mdl0037.htm

4:33 PM  
Anonymous Anonymous said...

Why not remove the Subreport all together.
Run a JOIN query on the Header and Detail items you need and then show the head in the report heading and details in the report detail. Header information will show only in the header as report header doesn't display multiple lines.

11:28 AM  
Anonymous Anonymous said...

Just remove the master and child link.
Since you are pulling records in sub form based on main form anyways, a link is not needed

2:54 AM  

Post a Comment

<< Home