Use a form to query a report

  • Thread starter Thread starter Randy
  • Start date Start date
R

Randy

I need to be able for staff to have a choice of fields in a form to
pull up data in a report. In Approach you can just use any form or
report click on a FIND button and then go to the report.

The key is that I need for staff to be able to enter any or all of the
fields to drill down to the selected data in the report.
From what I can see in Access if you create a query you must use the
Click to expand...
field where you have set parameters or it will not give you the data or
will keep asking you for the parameter data.

How can I set up a form in Access 2000 to select data then go to a
form? Some of the fields may or may not be entered when doing a query
for data.

I am using this newsgroup as I am not familiar with code and need an
example or some simple directions.

Appreciate someone putting me in the right direction.
 
Randy,
I think your referring to Approach's use of the "Current" form filtered
dataset as the recordsource for your report.

I set up the following logic to mimic that...
(The report is based on query that delivers "all" records in a table to
the report)
1. If the form is not filtered, then just report on the record being
displayed.
2. If the form is filtered, then apply the same filter as the form to
the report.

That allows the user to "drill down" by filterbyform, or
filterbyselection, etc... and then run the report.

Here's the report code: (CustID is the example key field)

Private Sub Report_Open(Cancel As Integer)
If Forms!YourFormName.FilterOn = False Then
Me.Filter = "CustID = Forms!frmYourFormName!CustID"
Me.FilterOn = True
Else
Me.Filter = Forms!frmYourFormName.Filter
Me.FilterOn = True
End If
End Sub

hth
Al Camp
 
One approach is to make an unbound form (I'll call it frmFind). Set its
Modal and Pop-up properties to Yes. Create unbound text boxes or combo boxes
for the fields. Give them simple, short names such as txtFName, txtDOB, etc.
In the parameter query (design view) set the parameter to:
LIKE Forms!frmFind!txtDOB
OR Forms!frmFind!txtDOB Is Null
for each field (using the appropriate control name from frmFind). This will
work for maybe 5 or 6 criteria. If you need to do more than that you may
need another approach, but since you have not been specific I will leave it
at this.
The way this can work is that the command button to open the report actually
opens frmFind. After entering the criteria a command button on frmFind could
contain code like the following:
DoCmd.OpenReport "rptMyReport", acViewPreview
Me.Visible = False

In the Close event for the report you can close frmFind:
DoCmd.Close acForm, "frmSpecify"

You can modify the procedure to fit your needs.

Also, be aware that you can click into a bound control and press Ctrl + F on
the keyboard to bring up a Find dialog box, or you can right click and choose
Filter by Selection. Not as intuitive, but very effective, particularly with
users who will be in the database frequently (and are therefore more likely
to remember shortcuts than are occasional users ).
 
I am not real clear on the section below.

In the parameter query (design view) set the parameter to:
LIKE Forms!frmFind!txtDOB
OR Forms!frmFind!txtDOB Is Null
for each field (using the appropriate control name from frmFind). This
will
work for maybe 5 or 6 criteria. If you need to do more than that you
may
need another approach, but since you have not been specific I will
leave it "

I do have six fields I would like to put on the form. When I create
the unbound form and then the unbound text fields I can click open the
properties and then click on the event procedure and it will bring me
up to a query, but all the fields in the column are blank. What do I
need to do from here to complete the parameter query according to your
instructions. This is where I am getting stuck.
 
From your response, I suspect you might not have a lot of experience with
Access. So... I'd like you to reconsider my previous solution... as being a
bit easier to understand and implement.
If you don't feel your users will be able to handle "Filtering by form"
or "Filtering by selection" then Bruce's suggestion would be the best
choice.

1. My method allows the user to use the "Filter by Form" or "Filter by
Selection" function to decide what records will be delivered to the report.
If you filter for City, the report will be filtered for City. If you filter
by City AND State... the report will filtered for City AND State.
2. No need to build a seperate form to define Parameters for the report
query.
3. Instead of just 6 parameters, you can filter your form by as many
factors as necessary.
4. If your form is NOT filtered, the report returns just the "active
displayed" record.
4. It's quick and simple to understand and implement.

Use the normal form that you use to view your records (RecordSource = All
Records)

Create your form and use the same RecordSource as your form (RecordSource =
All Records)

Place this code in the Open event of your report... (using your form
name)
Private Sub Report_Open(Cancel As Integer)
If Forms!YourFormName.FilterOn = False Then
Me.Filter = "CustID = Forms!frmYourFormName!CustID"
Me.FilterOn = True
Else
Me.Filter = Forms!frmYourFormName.Filter
Me.FilterOn = True
End If
End Sub

hth
Al Camp
 
The reason I said 5 or 6 fields is that there is a limit to the length of a
SQL string. That is to say, behind a query is text that you can see by
opening a query, then clicking View > SQL view. I don't know just what the
limitation is, but you can only have just so many characters in a SQL string.
Short names on the unbound form will help in this regard.
But forget the unbound form for now. First, design a parameter query for
use as the report's record source. See Help for more on this. You have not
mentioned the report's purpose, so for the purposes of what follows I will
assume it is a listing of people, including names and addresses. If you want
to find only those records for people who live in a particular city, then for
the criteria for the City field you would put [City] (or whatever text you
want). If you want the option of showing all records, you would put LIKE
[City] OR [City] Is Null. If you open the report, you will be prompted for
parameters (in this case the name of the city). Make sure it works as
intended.
Now for the unbound form. Create a blank form (frmFind) in design view,
then add a single text box named txtCity. Add a command button. In the
command button's Click event put:
DoCmd.OpenReport "rptYourReport", acViewPreview
Me.Visible = False
Back to the query, substitute [Forms]![frmFind]![txtCity] for [City] in the
parameter. To open the report (let's say from a form), place a command
button on the form with the code:
DoCmd.OpenForm "frmFind".
When the user wants the report they will be opening frmFind. Once the
parameter (City) is entered in frmFind, a command button opens the report.
Since the report is based on a parameter query, the query will start by
looking for the parameter, which it will find on frmFind.
Instead of a text box on frmFind for [City] you could have an unbound combo
box. The Row Source (not the record source) could be a query based on the
main table that presents the user with a list of cities. Instead of typing
Boston in the text box the user can select it from a list. It still has the
effect of showing only those records for which the city is Boston.
When you close the report you will probably want to close frmFind.
 
In all the suggestions I am getting it appears that I have to set up
specific parameters to get the data. In Lotus Approach you could click
on a FIND button on a form it would bring up the form with blank fields
and you could type in one field or any field or all the fields and it
would bring up the data you specified. Are you saying I have to
delineate in code the specific parameters to get to the data. Is there
not a way for Access to be as flexible as Approach or am I missing
something? Based on the last example it only filters by CustID and
does not allow any other choices. If I add other choices it does not
allow me to chose only one field.
 
Randy,
You must check out "filter by form" in Access help. It does the same as
the Find in Approach.
Once you understand and can use "Filter by Form", then use my report
"filtering" suggestion to match the report results to the filtered recordset
of your form.
hth
Al Camp
 
Back
Top