Filter report based on continuous form

[Jay in PA]

Hi,

I have a report that a checklist for a given user. My challenge is
that I would like to have a single form in which all user checklists
are available (e.g., the same checklist/report format iterated across
all users). For each user, I'd like to have command buttons that allow
the user to view, print, or e-mail the report. The difficulty is that
the users will vary--users will be added to, and dropped from the list
every day, so I can't hard-code the reports to the command buttons.

My questions are:
1. How can I display the list of current users with appropriate
controls to view, print, or e-mail their checklist reports? My first
thought was to use a continuous form as a subform.
2. How do I dynamically generate the report before printing or e-
mailing it? For example, I know I could use DoCmd.SendObject to e-mail
the form, but how do I dynamically generate it based on the selected
user before e-mailing it?

Thanks to anyone who can make sense of my rambling questions and offer
a solution!

Jay

 

[Jackie L]

Jay,

If you do not have a user table yet, you will need to add one. I am
thinking your design suggestions are more complicated than they need to be.

If you create a form (Single view) with an unbound combo box (UserID).
Attach your user table to that drop down so that the current users are the
only choices. Make the combo box - limit to list - Yes.

Now, add an option group to your form with all the reports you would want
the users to have access to (name it Options). Each field/report in your
option group will now have a value (1,2,3, etc).

Add three command buttons to your form for View, Print and Email.
Base all of your reports on queries which use the unbound UserID field as
criteria.

Then on the OnClick event of your View button, you can put:

If Me.Options = 1 then
DoCmd.OpenReport "rptFirstReportName", acViewPreview
DoCmd.RunCommand acCmdZoom100
End IF

If Me.Options = 2 then
DoCmd.OpenReport "rptSecondReportName", acViewPreview
DoCmd.RunCommand acCmdZoom100
End IF

(The Options number corresponds to the OptionValue on each report).

Continue for each report...
Do the same for the OnClick of the Print button except the code would be:
DoCmd.OpenReport "rptSecondReportName"
(with no second line)

If you get this, then the code for emailing the report is more involved but
we would be happy to help with that.

If you need a password by user, let us know on that also.