2 novice design questions

[Sarah]

I have been asked to refurbish a database used at my work. I'm quite new to
Access, but seem to be getting the hang of it. I have 2 general questions
about database design.

1) The data base currently has a form called frmPrintMenu, containing 9
buttons. Each button prints a different report. (rptTeachingStaff,
rptCustodialStaff, etc.) This works fine, but the user has to open the
PrintMenu form everytime she wants to print a report. I'm just wondering if
it would be better to decentralize these print buttons, placing them on each
of the data input forms. (frmTeachers, frmCustodians, ...) Generally, is
there an accepted standard or any preference for this sort of thing?

2) Each button on frmPrintMenu opens an intermediate form with a combobox.
Once a selection is made and cmdPrint is clicked, the intermediate form
closes and the requested report prints. This works, but it seems like a bad
design to me because each of the 9 buttons on frmPrintMenu opens a DIFFERENT
form. What I'd like to learn how to do is to always open the same form, tell
it how to populate its combobox, then, when its cmdPrint is clicked, print
the desired report. Can anyone out there offer me some guidance?

Sarah

 

[tina]

comments inline.

I have been asked to refurbish a database used at my work. I'm quite new to
Access, but seem to be getting the hang of it. I have 2 general questions
about database design.

1) The data base currently has a form called frmPrintMenu, containing 9
buttons. Each button prints a different report. (rptTeachingStaff,
rptCustodialStaff, etc.) This works fine, but the user has to open the
PrintMenu form everytime she wants to print a report. I'm just wondering if
it would be better to decentralize these print buttons, placing them on each
of the data input forms. (frmTeachers, frmCustodians, ...) Generally, is
there an accepted standard or any preference for this sort of thing?

no. the correct design here would be whatever best fits the workflow, and
makes the user's job as easy as possible. suggest you talk to your user(s)
and make your decisions based on their feedback and your own skill level.
basically that means that if they ask for something you don't know how to
do, you have to weigh their needs against the time it will take you to learn
how to do what needs to be done to meet those needs. always a judgment call.

2) Each button on frmPrintMenu opens an intermediate form with a combobox.
Once a selection is made and cmdPrint is clicked, the intermediate form
closes and the requested report prints. This works, but it seems like a bad
design to me because each of the 9 buttons on frmPrintMenu opens a DIFFERENT
form. What I'd like to learn how to do is to always open the same form, tell
it how to populate its combobox, then, when its cmdPrint is clicked, print
the desired report. Can anyone out there offer me some guidance?

before you make any changes to this setup, you'll need to investigate how
the various forms interact with the reports. otherwise you're almost certain
to "break" something. suggest you look at each report's RecordSource
property first; you're likely to find a query or a SQL statement. in either
case, you're also likely to find criteria on one or more fields that refer
to a specific formname and controlname. if that's not the case, look at the
code behind each button on those "intermediate" forms you mentioned; you're
likely to find that the OpenReport's WhereConditon argument has been used to
restrict records in the report.

once you understand how the various forms are being used to control the
reports, you'll pretty much understand how to change the setup safely.

hth

 

[Roger Carlson]

#2
On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ChooseReportFromList.mdb" which shows several variations on
how to do this. There is also one called ChooseReportFromList2.mdb which
shows a more advanced Report launch form.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L