Form to generate a report

  • Thread starter Thread starter Heidi
  • Start date Start date
H

Heidi

I'm sure there's a fairly straightforward way to do this, but as usual
I can't see it...

I want to use a form to select criteria to show on a report. For
starters, I don't know if the form should be based on a query or not.
What I have so far is this:

1. qryCategoryQuestions is the foundation of rptCategoryQuestions.

2. On a form, the user needs to select a choice from three combo
boxes. She does not have to select an item from each combo box, but
should select at least one from the first one, and possibly one from
all three:
cboTopic
cboCategory
cboSubcategory

3. The user also needs to establish a date range (from-to) for the
report from an dtmInquiryDateTime field.

4. Once all items desired are selected, click a button to preview a
report that shows only records with the selected Topic, Category
and/or Subcategory, and in the date range specified.

Any suggestions would be very much appreciated.
Thanks,
Heidi
 
Use the query to input your parameters and base the Report off this query.
Also Point the query to the form for it's parameters.
i.e. [Forms]![Monthly Statements]![Customer ID] (This is
in a query)
This is in a query used to open a Report. The form where it get's it
criteria is named Monthly Statements. This is used to open a report where
the customer equals Customer ID on your form. Either choose a macro or use
code to open the Report after entering parameters.

Hope this helps,
Dave
 
Gee, I like what you want to do!

I so often create forms like this to launch the reports. It means that the
one report can do a real lot.

It is also make things really easy for your users.

Here is some screen shots of what I mean by this:

http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html


so, create a un-bound form (one that is not attached to a table). Turn off
all the junk like navigation buttons etc. (that is what I did for the above
forms).

Next, remove all "conditions" from your query that you built. (you might not
have any conditions yet...but I simply want you to remove any conditions in
tat query that references the form).

Note how in several the above screen shots I simply put the instructions
under the combo box to simply leave them "blank for select all". I am going
to suggest you do the same.

For the start date, and end date, I will suggest you drop in two ActiveX
calendars.

In the forms on-load event, you can set the two calendars to today by:

me.ActiveStartCAl.Value = date
me.ActiveEndCal.Value = date

Ok, no place your Now, by removing all the conditions from the query your
built, the query is MUCH nicer. Further, by not having any forms references
in the query, you can use that query for more reports.

Now place your combo boxes on the form. You can even use the wizard to build
those combo boxes. Hopefully, you have a good normalized designs, and you
can thus use the same tables to fill those combo boxes as used in the data
entry forms.

Now, place a button on the form to "preview report" The code behind the
button is going to build the "where" clause. That clause is the same as the
sql where, but without the word where.

dim strWhere as string

' do the date range

strWhere = "dtmInquiryDateTime between #" &
format(ActiveStartCAl.Value,"mm/dd/yyyy")
strWhere = strWhere & "# and #" & format(ActiveStartCAl.Value,"mm/dd/yyyy")
& "#"

strWhere = "(" & strWhere & ")"

' now do > cboTopic

if isnull(cboTopic) = false the
strWhere = strWhere " and "
strWhere = "(Topic = " & cboTopic & ")"
endif

' now do > cboCatagory

if isnull(cboCategory) = false the
strWhere = strWhere " and "
strWhere = "(Catagory = " & cboCategory & ")"
endif

' now do > cboCatagory

if isnull(cboSubcategory) = false the
strWhere = strWhere " and "
strWhere = "(SubCatagory = " & cboSubCategory & ")"
endif

debug.print strWhere

docmd.OpenReport "rptCategoryQuestions",acViewPreview,,strWhere

In the reports on-open event, you might want to place a docmd.maximize.

In the small form, disable the min/max buttions, so the samll form will NOT
take maxizmie.

Also, the above code example assumes the combo boxes return a id (number)
value. If the combo boxes reutrn actaul text, then you need to surround the
values with quotes.

strWhere = "(SubCatagory = '" & cboSubCategory & "')"
 
This looks to be a beautiful solution, thank you! However, I'm
receiving a syntax error on the line:
strWhere = strWhere " and "
"Expected end of statement". I don't know enough about VB at this
point to know what the problem is; can you please clarify? Thank you
very much!
 
The only problem here is that I am forcing you to use some code!!!
strWhere = strWhere " and "
Click to expand...

The above should read

strWhere = strWhere & " and "

the "&" sing is used to concatenate the expression. So, we are just
"building up" the expression as we go along.
 
Back
Top