Using a switchboard form to open reports is good.
Opening them in "design" mode (if this is what you mean)
is not good.
Below are two approaches: The first is simpler than the
second. I roughly descrbed the first method in my
earlier reply. Here is more detail.
Create a query that contains the records you want to
report. Don't worry about limiting (filtering) the
records yet.
Design your report. "Bind" it to the query above by
selecting the query in the Record Source field (ALL tab)
on the properties sheet of the report.
View your report. If it looks good and it contains the
records you want to see (plus some you do not want to
see) than you are ready for the next step.
Select design view of the report and click once on the
Record Source field of the properties sheet. You will
see two small boxes on the right, one with a down arrow
and the other with 3 dots (...). Click on the one with 3
dots. This opens up the query builder tool.
Now is where I have to ad lib a bit, since I don't know
what you are using to limit the records. I will asume
you have a date field (I will call in InvoiceDate) in
your query and that is what you want to use to limit your
records - Like "Give me all orders from 12-31-03".
In the query builder, under the InvoiceDate column is a
grid area. Look for the one that says Criteria. In the
criteria area you would type text that the user will see
to limit the records enclosed in brackets. For example,
[Enter the Invoice Date]. Save your changes.
Now click on the query view and you will see a message
box with the text "Enter the Invoice Date" and a text box
for the user to enter the date. Enter some valid date
and click "OK". The query should run and return the
records you want.
You can be very creative with the query grid - you can
ask for a date range like this: BETWEEN [Enter Start
Date] AND [Enter End Date]. You can put brackets and
text on multiple fields. For example, if you only want
invoices from a certain date and a certain company add
the following in the CompaanyName criteria, [Enter
Company Name]. You will get two message boxes, one for
the date and another for the company name.
If this works, save you changes. Now whenever you select
the report, it will automatically bring up the message
boxes because the report is tied the the query.
Make sure your switchboard calls the report in Normal
view, not design view.
As for the second method it involves using VBA, something
I highly encourage you to learn. It opens up so many
possibilities within Access.
The method is add some code to the form you call from the
first form (I called it InputForm in my first reply) that
sets a filter and turns the filter on. Rather than
trying to explain it I will give you one web page and I
suggest you google on ACCESS VBA FILTER SYNTAX. Also,
there are a lot of good books that can teach VBA.
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=9037
Let me know if this helps.
-----Original Message-----
Randy, Thank you for your reply. Yes, I am using .mdb.
The final DB will have a switchboard that will list all
reports. When you click the button it will open the
report design. The first thing that the report design
does it open a 2nd form as input to the query. The data
from the 2nd form IS passed to the query and the query
shows the selected records. My problem seems that the
query doesn't tell the report design that it's done and to
format the data and display / print the results.
As to your question about writing code for filters, no I
do not know how to do that.
Thanks for your input and help!
Jeff
-----Original Message-----
I will dovetail on Howard's reply. I will also assume you
are using a .mdb database and not a .adp project.
You do not specify how you execute the report so I will
make some assumptions.
First, execute the report from a form, not by clicking on
the report in the database window.
Say you have a form "ReportForm" with a command button to
call your report "Report1". Do you have something in the
criteria field in the query the report is based on that
filters the data, say [Enter Invoice Date] in the date
field criteria of the query? This should work everytime.
Perhaps you want to create a more sophisticated (or
elegant) look to the input. In that case you can use a
second form. Here, your command button on the ReportForm
calls the second form "InputForm", perhaps as a modal.
From here you write code that responds to the input. For
simplicity, say it is still date. In this case, you would
set the record source of the report to the query and use
the Filter property to limit the records.
Are you familiar with writing code to create a filter and
then turn it on and off? If not, provide more specifics
and someone in this group can help.
Last, I would not close the Input form until I was done
with it. It is musch easier to call data from an open
form than a closed one. Instead hide it with:
Forms!InputForm.Visible = False. This way you can easily
use the input criteria to filter. Just remember to close
the form, usually in the Close event of the Report.
Does this help?
Last
-----Original Message-----
Howard, Thanks for the reply. I'm not sure that I
described the problem correctly. I have designed a report
that when executed it will open a form, ask the user for
input. When I click the command button on the form the
form closes and I'm back to the DB window. However,
if
.
