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.
posted by Alex Dybenko | 14:39
16 Comments:
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?
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
How do you create a "temporary local table" from a pass-through query?
Thanks!
Just run a Select Into (or make-table) query, based on pass-through query
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
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
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
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
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
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
hi,
in this case you have to use:
Forms!form1!MySubform.From.RecordSource = "tableA"
Thanks Alex !! Took a little time but I've finished.
Thanks a lot !
HA
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
Hi,
you can look at this link for a sample code:
http://www.mvps.org/access/modules/mdl0037.htm
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.
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
Post a Comment
<< Home