Data Source for a Sub-Report

[Don]

It might be beneficial to describe the problem before going into details of
the problem .......

The main report lists details about all positions within the company.
Included is a list of people who are assigned to that position. Normally,
only one person is assigned, but sometimes due to promotions, retirements,
transfers, etc two people might be assigned to the same position. Each
position in the position table has a unique ID number (in field PositionID),
and the record for each individual has a position field (PositionID) in
which the appropriate position number is placed.

Currently the report uses a SQL string to get the position data for the
report (Me.RecordSource = strSQL) from a table called tblPosition. On the
report is a sub-report that has information about the employees assigned to
the position which gets its data from a table called tblPersonnel. The
sub-form is linked to the main form using the PositionID field of the SQL in
the main form and PositionID field of tblPersonnel which is the data source
for the sub-form.

The folks that use this report have asked for some variations of this
report. For example, a report containing only cases where the position is
assigned to a particular department (in tblPosition) and the employee is
assigned to a different department (in tblPersonnel). To get this data I
have created an SQL query that provides all the results. However, the
sub-form is still getting its data directly from tblPersonnel, so if the
position has two people assigned to it, one in the same department as the
position and the other in a different department, both are listed!

What I am thinking, but don't know how to proceed is to have the sub-form
only use data flowed down to it from the main form. The problem being how
to link the sub-form to that data and not tblPersonnel.

Am I barking up the wrong tree here? Any ideas?

Thanks!

Don

 

[Douglas J. Steele]

Can you apply a filter to the subreport?

 

[Don]

Doug,

I guess I am enough of a MS Access novice to not really understand your
question.

Here are a couple of additional details:

1) The report is common and the exact output is driven by the SQL being
tailored in the OnLoad function of the form. The OnLoad function receives a
variable from the calling command button (e.g. DoCmd.OpenReport stDocName,
acPreview, , , acWindowNormal, "Full"). Titles, etc are also set during the
OnLoad. Aside from having only one form to maintain, the goal of using the
SQL is that we are pondering upgrading the backend database (with
tblPosition and tblPersonnel) from another MS Access database to a MS SQL
Server based solution (thus minimizing the modifications to the Front End
database).

2) Here are a couple of "sample" reports:

Full Mode Report:

Positon 1 Department #1 Blah Blah Blah
Assigned Personnel
Worker, John Retiring Department #1 Blah Blah

Positon 2 Department #2 Blah Blah Blah
Assigned Personnel
Doe, Jane Department #2 Blah Blah
Smith, Jane Department #3 Blah Blah
........



Differing Departments Report

Positon 2 Department #2 Blah Blah Blah
Assigned Personnel
Smith, Jane Department #3 Blah Blah
........

Hopefully, this helps understanding my problem!

Thanks!

Don