Looking for design ideas ...

  • Thread starter Thread starter BobC
  • Start date Start date
B

BobC

I am new at Access and using Access 2000.
I will have about 6 tables and will be creating about 13 reports (based
on queries).
Thanks to the recent efforts of "Duane Hookom", I now have my first 2
reports working in a scaled down version.
I now find it necessary to provide date information to the queries.
(i.e. be able to base the reports on beginning and ending dates provided
by the user at the time the reports will be printed.)
It would be nice to have a basic menu from which to generate the
reports, which automatically requests any needed data (such as dates),
prior to the printing of the reports. I was thinking I can possibly use
forms (one main form with subordinate forms) for this purpose?
I have written a few VB6 programs, but would like to keep this as simple
as possible.
The use wizards and dragging and dropping is a lot easier than writing
the code, if I can get away with it. Keeping the files and code small
and fast is not important.

Before I launch further into this, I would appreciate the suggestions of
those who are more experienced!
 
BobC said:
I am new at Access and using Access 2000.
I will have about 6 tables and will be creating about 13 reports (based
on queries).
Thanks to the recent efforts of "Duane Hookom", I now have my first 2
reports working in a scaled down version.
I now find it necessary to provide date information to the queries.
(i.e. be able to base the reports on beginning and ending dates provided
by the user at the time the reports will be printed.)
It would be nice to have a basic menu from which to generate the
reports, which automatically requests any needed data (such as dates),
prior to the printing of the reports. I was thinking I can possibly use
forms (one main form with subordinate forms) for this purpose?
I have written a few VB6 programs, but would like to keep this as simple
as possible.
The use wizards and dragging and dropping is a lot easier than writing
the code, if I can get away with it. Keeping the files and code small
and fast is not important.
Click to expand...

Definitely use a form for this purpose.

Add a couple of text boxes where the user can enter the date
range (click on text box tool in the tool box toolbar, then
click & drag in the form's detail section). For the sake of
discussion, let's name these text boxes txtStartDate and
txtEndDate (click on a text box and display the property
sheet, then type the name into the Name property).

Now, back at the tool box, click on the Command Button tool.
This should bring up the command button wizard to allow you
to select Report Operations - Preview Report. Complete the
wizard prompts to get the wizard to generate the VBA code
for the command button's Click event procedure. Then, you
can view the code (View - Code menu) and modify it to look
more like:

Dim stDocName As String
Dim stWhere As String

stDocName = "yourreportname"
stWhere = "[yourdatefield] BETWEEN " & _
Format(txtStartDate, "\#m\/d\/yyyy\#) & " AND " & _
Format(txtEndDate, "\#m\/d\/yyyy\#)
DoCmd.OpenReport stDocName, acViewPreview, , stWhere

using your own names for the various entities, of course.

That should get you started for reports that work from a
date range. Once you've specified the requirements for
other parameters in the various reports, you'll probably
have more questions about how to combine things, if so, post
back with a detailed explaination of those requiremnets,
tables, fields, etc.
 
Back
Top