Form with Subreport and setting record source.

[Monterey_Manzer]

Hi Everyone,



I may be asking too much of my proposed form but I thought I would try. I have a database of "trackers" that my office uses to keep track of various issues with our program clients. All these trackers, the forms and the reports that are based on these tables, are issue specific. I am attempting to build a field that will allow you to view all records in each of the tracker tables associated with a specific client ID number. So here is what I am thinking:

I want to build a form with a subreport on it. This subreport would be blank or not visible at the outset. The user would enter a client ID number, and then select which tracker they wished to view from a combo box. The selection in the combobox would change the Source Object of the subreport. The subreport would pull up a version of the ordinary tracker report with an additional WHERE statement like "WHERE rptTracker.ClientID = Me.ClientID.Value".

Another option besides selecting the Source Object with a combobox would be having multiple subreports in a single form who all have their record source updated once the ClientID is entered. However, I want to cut down on the amount of objects on this form so as not to slow its performance.

Any suggestions?

Thanks!

Ryan.

[orange]

Do you have a database design? Can you show us you tables and relationships as a jpg?
Can you tell us more about your environment, and your Issues and Clients?

We only know what you tell us.

The idea of filtering records , such as your

"WHERE rptTracker.ClientID = Me.ClientID.Value"

, seems very reasonable.
And could even be expanded to include some IssueDate, IssueType, Location or whatever other parameters/attributes are involved.

[Monterey_Manzer]

The environment is pretty straight forward. The "clients" are college students and we track things like receipt of their transcripts, whether they complete internships, or if they are ever put on academic probation. Each of these "trackers" has it's own table. For those tables like Transcripts where every client should be on there and only on there once, the ID number is used as the primary key (e.g. Transcripts Tracker). In other situations, the client ID is a foreign key which relates each of the tracker tables to the client information table.

I have reports for each specific tracker that pulls in information from the client information table that provides info relevant to each topic. However, as I stated, each of these reports focuses on a single issue tracked and you scroll through the records to view the data on each client. A few of the forms used to enter or change data in each of these trackers have buttons for filtered reports. For instance, our Transcript tracker form has a button which, when clicked, displays a report of the basic information we have for the university attended by that specific client (things like term schedule, GPA scale, number of units required for full time status). What I am trying to make is a form that allows you to display any of the reports, in a sub report, for our trackers filtered to a specific client ID. I think this will allow users to get a better whole picture of specific clients.

I hope this makes things a little more clear. Thanks!

[orange]

I can assure you that "pretty straight forward", isn't very often. It's very familiar to the poster, but for readers we only see what you tell/show us. And most posters talk in "jargon of their environment" not business terms.

Each of these "trackers" has it's own table

relates each of the tracker tables to the client information table.

Do you have a database design? Can you post a copy of the relationships?