Instantiated Reports (or better alternatives?)

[Dunro]

Hi there,

Has anyone out there had success with instantiating reports? In past, where I've tried to do it the report just closes semi-unexpectedly after changing recordset (or filter).


My current project uses reports for one type of screen because a continuous form can't be "grouped." These reports show different information and need to be open at the same time. (e.g. comparing 1 "case file" against another "case file").

I've avoided this issue in the past by simply maintaining multiple copies of the report (depending on if they are using the "local" table or the "network drive" table), but this seems messy.

My ideas for alternatives are:
1) Continue maintaining multiple copies manually
2) Have each form instance that might want to open this report create a temporary copy of the report, then close/delete the report when the form closes.
3) Find a workaround to group continuous forms? (This would be ideal actually).

Any thoughts as to how to do this better would be appreciated.

[rpeare]

So all of your data is coming from the same source you just want to change things like sort order/fields that appear? or is it something more substantial than that, because it's possible to programatically change the sort/grouping of a report based on user input.

[Dunro]

A little bit of both, frankly

I have 1 form called Case Details, which is instantiated per Case ID (so I might have Case 1 and Case 2 open at once). From Case Details, I might click "View Linked People" (currently a report) which lists people broken into sections - so Client contacts, Case managers, Case manager's manager, etc. and other people involved in the case.

In an ideal world, this report could be open twice so that I can review 2 files at once. Unfortunately, instantiating reports (Set xyz1 = new [Report_XYZ]) does not appear to be very reliable when filtering or changing recordsources, or changing order, etc.

I also use a similar report when creating a new file (which uses a separate local table until the entire case file is ready to be copied to the proper backend tables, due to network performance issues). Sorry for the confusion!

[rpeare]

Yeah, I don't think I'd even try to open the same report twice with different sorting/grouping, if this were truly an issue I'd likely create two reports rptView1 and rptView2 and apply the sorting/grouping to them individually rather than just having rtpView1. Perhaps, it might be a better idea to have a 'review' form where I could pick the 2 different cases and display the people associated with them rather than trying to open the same report twice or two different reports. Probably not what you want to hear but I am not a fan of instantiating, perhaps someone else may have a better solution.

[Dunro]

I'm trying to use instantiation / object-oriented design principles where possible - partly to keep technical documentation consistent, etc., realizing that Access is not object oriented

I would much prefer a form with group headings... but unfortunately, no one at MS thought of that

[rpeare]

You can create your group headings/sub headings in a form though. Either through the query driving it or through VBA code that is populating an unbound form.

[Dunro]

I'm not sure I understand how I would add heading rows to a query (or form... but likely query would be useful). I've attached an example of the raw data and the desired display. (The raw data is all foreign keys to other tables, but I can deal with that).



The resulting screen is where one can remove said links (or launch a form to create a new one). There would actually be 3 levels of headers, but I've only included one level in the desired output attached. Any insight is greatly appreciated (realizing this isn't the SQL forum).

[rpeare]

So let me ask a question, your goal is to compare two different cases to see who's assigned to them.

Will these two different sets of data always have the same LinkGroupID's, LinkSubGroupID's and LinkTypeID's, it's only the PERSONID that will change?

Are you visually comparing these or would it be better to have a visual aid like (say you were comparing FileID 143 to FileID 144)

LinkGroupID	LinkSubGroupID	LinkTypeID	PersonID	File143	File144
1	103	1	14617	Yes	Yes
1	103	1	14444		Yes
1	104	1	218793	Yes
1	104	1	999998		Yes
1	106	1	21402	Yes
1	106	1	21418	Yes	Yes
1	106	1	220299	Yes
1	106	1	999997		Yes
1	106	2	14557	Yes	Yes
1	106	3	21322	Yes
1	106	3	219316	Yes
1	106	3	999996		Yes
1	106	3	999995		Yes
2		1	5858	Yes
2		1	999994		Yes
3		15	2104	Yes	Yes

If your goal is just to have a visual aid to see who is assigned to what between 2 (or 3 or 4 cases) I would definitely not do this with reports, that leaves a lot to be determined by visual means where if you create a form or query to drive the comparison you can look strictly at people who are assigned to more than one fileID or you can arrange something like what I'm showing particularly if people can be assigned to different linkgroups, linksubgroups or linktypes so let's say person 14617 is assigned to linkgroup 1, linksubgroup 103, linktype 1 for case 143, but assigned to linkgroup 1, linksubgroup 106, linktype 3 for case 144, is that important? or can it happen? so instead of visually going through each person ID in case 143 and comparing it to each person ID in case 144 you can have the computer do it for you.

[Dunro]

To clarify, the end goal is to have different "People Link" windows open simultaneously from different "Case File" instanced forms. Being constrained to 1 "People Link" window is a pain for various reasons, including when trying to review 2 files at once.

I don't use "People Link" windows for reporting, it's more to provide a user-friendly way of changing who is connected with a case. Sometimes it's useful to look at more than 1 case at a time. (The current report has an "End" button beside each person, as well as an "Add New" button in the header).

This is going to sound convoluted, so bear with me. We have to do a fair bit of slicing and dicing with our data and this shows what cases everyone has (usually 1-106-1) and who they are working with in various capacities.

We track cross-functional work assignments (LinkGroupID = 1 is everyone working on it at our organization, LinkGroupID = 2 is who the client contacts are, and LinkGroupID = 3 is another stakeholder). SubLinkGroup is who is working on it from each function in the organization, and LinkType is more specifically their role (Lead, Backup, Manager) within each function. PersonID is the person's unique identifier.

It works nicely because we can have 1-106-1 be the lead from one function, 1-103-1 be the lead from another function, along with 1-103-3 being a Manager who's involved from that function. 2-<nulll>-1 be the Client's primary point of contact. Therefore, the use of all 4 fields varies by case file.

"People Link" windows are more to go through who is linked to a given file (front end user interface). It often gets launched to add/remove types not already accessible through a subform (1-106-1, 1-106-2, 1-106-3, 2-<null>-1, and 3-<null>-15 can be set from a subform).

If I am doing large scale auditing or cleanup, I use queries to identify the exceptions.... but this is more to help the end user who may have 2 cases on their screen at once.

[rpeare]

Is there any chance you can create a sample database with some fictional data, I think something's getting lost in translation and it might be easier to see an example of what you've got so far.

[Dunro]

I don't think I can de-sensitize my test environment enough and still have it make sense, but I've created a screenshot to show how the "People Link" window works.

It's been working, I'm just trying to find some better options..

edit: Sorry, didn't realize I had shrunk it so much... The joys of last minute editing in Paint

[rpeare]

I apologize I can not help you then. It seems you have sample data in there but honestly I still think I'd perform the function by using a form to show both records. The original question was basically how do I open the same report twice with different data on each and short of receiving a sample my best suggestion is that you have two reports, and when you click your people button check to see if report1 is open, if it is NOT open report1, if it is, open report2

[Dunro]

I appreciate the insight all the same, rpeare. You've given me the idea to try adding heading separators to a query, so now I'm weighing the pro's/con's for either creating temporary reports (for cases where I can't instantiate), and/or rewriting a query to include header separators that can be used with conditional formatting in a continuous form.

I'll leave this up for a bit longer in case anyone else has feedback to the original post.