Access Upsizing - Populating Access Reports in Unlinked MDB Front-End
-
Hi people.
I hope you can help me over this hump.
I have used DTS to import tables into SQL-S 2000 and have written quite a lot of SPROCS and UDFs to replace (a) linking to tables in redundant Access .MDB files,
(b)VBA Code to fill and manipulate data in the SQLS tables.
Now, I'm working on the Access reports. I have successfully created a SPROC to fill a simple report with SQLS data, using VBA code and a Pass-Thru query to execute the SPROC to get the recordsource.
MY next move, (ie the problem) is that several of my reports have nested sub-reports, down to 3 levels. ie, Main report > subrpt_1 linked to a value in Main report, then >subrpt_2, linked to a value in subrpt_1.
In Access this is quite a "clever" report, but I'm not sure how to set up the recordsources in proper synch with each other.
I understand the sub-reports are populated bottom nesting level first, and the Main report last. Is this correct?
Has anyone had to tread this path before? (I bet someone has.)
I cannot find a text-book anywhere to cover this subject. Those I have seen do not seem to have heard of sub-reports as relating to SQLS databases.
Any ideas or experiences, things to watch for, etc are most welcome.
REgards,
Lester Vincent
Sydney
-
I'm guessing from what you're saying here you're talking about "drill down" reporting which might be a better term to search for.
if you're using msaccess to provide reports against an SQL2000 databases then this should be quite strightforward.
populate the top level data (the main screen you show) using your pass through query and stored procedure. then build an "on click" event for one of the fields in your report.
this on click event can do the following
open up the querydef for the second report, then set the parameter value for the stored procedure as the value you clicked on
then open the second report
something like
dim parvalue as int
parvalue=me.txtemployee
dim qd as new querydef
dim db as database
set db as currentdb()
set qd=db.querydef("query_for_secondreport")
qd.text="exec sp_myproc2 "+cstr(parvalue)
db.close()
docmd.openreport "secondreport"
although my code might not be 100% accurate as i'm away from my reference material at the minute
you can then repear this procees in the second report doing the same again into the 3rd report.
MVDBA
-
I'm guessing from what you're saying here you're talking about "drill down" reporting which might be a better term to search for.
if you're using msaccess to provide reports against an SQL2000 databases then this should be quite strightforward.
populate the top level data (the main screen you show) using your pass through query and stored procedure. then build an "on click" event for one of the fields in your report.
this on click event can do the following
open up the querydef for the second report, then set the parameter value for the stored procedure as the value you clicked on
then open the second report
something like
dim parvalue as int
parvalue=me.txtemployee
dim qd as new querydef
dim db as database
set db as currentdb()
set qd=db.querydef("query_for_secondreport")
qd.text="exec sp_myproc2 "+cstr(parvalue)
db.close()
docmd.openreport "secondreport"
although my code might not be 100% accurate as i'm away from my reference material at the minute
you can then repear this procees in the second report doing the same again into the 3rd report.
MVDBA
-
Thank you, Mike. I was not thinking of a drill-down so much as a simple main report with two levels of nested sub-reports. But I didn't know click events existed in report controls. Have never sought to use any.
I have printed out your reply, and will play around with it.
Again thanks, "Old Hand". (I'm 70, just a babe with SQLS.)
Lester Vincent
Sydney
-
Click events do NOT exist in Access reports. If you want true drill-down capability, you need to either build a data access page in Access (a tiresome exercise in futility, IMHO, unless you happen to already be comfortable with ASP and VBScript), or shift to Crystal.
However, I do understand that that's not what you are asking for. Unfortunately, my experience was that I played with it for a few days, couldn't get it to work, and attacked the problem from a different direction.
If you want to use Access' native linking for subreports, you need to use views for your datasources. Which, from the description of your problem, might not be viable.
Sorry I couldn't help more.
-
apologies - marshall is correct about click events - as i said i was away from my workstation so couldn't check
you might be better shifting to reporting services which will support the sub queries much better. especially in light of the fact that you've moved all your other components to SQL
MVDBA
-
Thank you, Marshall and Michael. I will keep working at it, and will advise the outcome.
I have to stay with the Access .mdb front-end at this stage. Maybe the answer is to open the Main report first, with no recordsources for the sub-forms, then in the Main report's _Load event, run some SPROC-calling VBA code to populate the two subforms.
If the news is any good, I'll post what has worked, if you think others would be interested.
Happy Easter to you both.
Lester Vincent
Sydney
-
I guess you are saying that you can't switch to an Access .adp, which would give you the ability to set up the subreports using stored procedures driven by a parameter based on a field in the report one level higher (this would be the method I would prefer). But using an .mdb it should work if you have the appropriate view or SQL statement as the sub-report's data source and just set the linking fields (parent and child) to the appropriate field(s). If you had performance problems with this approach, then you could go with setting the data source programmatically each time the subreport was formatted, but I would try the easy way first.
Dick
-
I'd like to ask you about the moment just before you actually open your report. At that moment, do your tables exist, the ones that you need to show up in your reports?
If the answer is yes, then there is no big issue. Behind the scenes the reports do populate out of sequence, but that is unrelated to your work in preparing the tables. For your purpose, it is sufficient to have all the tables populated just before opening the report. If you have that option, then you just open your report, let the report engine worry about retrieving related data, and your report works fine. That assumes, of course, that you use the linking mechanism available on a subreport control, where you specify the fields in the master and child that link the data together.
Does that help you at all? If not, can you say a bit more about your special situation so that I can take another crack at this for you.
RW
-
Hi Dick and Charles. I have tried twice (Satruday and today) to post a reply to you, but each time it has failed. Don't know why this is happening. This short message is really a trest, to see if it happens again.
Cheers,
Lester Vincent
Sydney
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply