Re: Data Source for a Sub-Report

From: Don (someone_at_somewhere.net)
Date: 02/20/05 

Date: Sun, 20 Feb 2005 15:56:21 -0500

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:un2zKt4FFHA.1476@TK2MSFTNGP09.phx.gbl...
> Can you apply a filter to the subreport?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
>
> "Don" <someone@somewhere.net> wrote in message
> news:O2Ryog4FFHA.3972@TK2MSFTNGP15.phx.gbl...
> > 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
> >
> >
> >
> >
>
>