Access 2007: Filtering a report's results using a drop-down box

Question

Hello folks -

My question is twofold.

• I have around twenty assorted tables in a database. The table layouts are diverse; the one common thread is that all of them have a 'County' field.

I need to set up a series of reports which allow a user to select a county from a drop-down box, triggering the report to run and return only records attached to that particular county.

This is doable at the datasheet level using a filter-by-form, but that's pretty clunky and I have several tables/queries which will need this same county filter.

I may be halfway there with the following:

---

Create an unbound form. Add a combo box. Set the Row Source of the combo box to include the County field. Set its Bound column to 1. Set its Column Count property to 2. Set the Column Width property to 0";1" Name the Combo Box 'ChooseCounty'.

Add a Command Button to the form. Code the button's click event as follows:

(Note: To write the code, in Form Design View select the command button. Display the button's property sheet. Click on the Event tab. On the On Click line, write:

[Event Procedure]

Click on the little button with the 3 dots that appears on that line. When the code window opens, the cursor will be flashing between two already existing lines of code. Between those lines, write the following code.)

Me.Visible = False

Close the Code window.

Name this form 'ChooseCounty'.

In the Query that is the Report's Record Source [County] field criteria line, write:

forms!ChooseCounty!ChooseCounty

Next, code the Report's Open event: (Using the same method as described above)

DoCmd.OpenForm "ChooseCounty", , , , , acDialog

Code the report's Close event:

DoCmd.Close acForm, "ChooseCounty"

When ready to run the report, open the report. The form will open and wait for the selection of the Company. Click the command button and then report will run. When the report closes, it will close the form.

---

I can persuade the report to trigger the form, but only once - I can't seem to figure out where precisely the 'forms!ChooseCounty!ChooseCounty' needs to go. Perhaps someone can clarify or offer a more elegant way to do this?

• I need to set up a large meta-report containing sub-reports on all of the tables - and, using the same drop-down 'choose a county' form, I need to have that choice cascade down through all the subreports. I don't have the faintest idea how to go about this. Suggestions welcome!

~ T

Answer 1

You seem to be asking two questions, the last of which is clear to me, but the first is not. The second one is in regard to how to cascade the filter to the subforms. You can do this in one of two ways:

1. put the form control reference as criterion in the recordsource of each subreport, OR

2. create a non-visible control on the report that has as it's controlsource "=Forms!ChooseCounty!ChooseCounty". Name that control "CountyFilter". Then, add CountyFilter to the link properties. If, for instance, you are linking the subreports on ID, you'd have:

3. LinkMaster: ID;CountyFilter

4. LinkChild: ID;County

(assuming, of course, that ID is your link field for the child reports, and that "County" is the name of the field in the child subreport).

Now, I'm wondering why you would have the County data not just in the parent record but in the child records -- that makes no sense. If you do have it, then the solution above will work.

If you don't, then I don't understand the question, as the whole idea behind subreports is that they are filtered by the parent record, so if the parent record is a person, and you filter by COUNTY, you're only going to get the child records in the subreport for that person, which by definition are already filtered by COUNTY because the parent has been filtered.

As to the earlier question, you write:

I can persuade the report to trigger the form, but only once - I can't seem to figure out where precisely the 'forms!ChooseCounty!ChooseCounty' needs to go

You have two choices:

1. hardwire the recordsource of the report to use the form control reference, so the WHERE clause of your report would be "WHERE County=Forms!ChooseCounty!ChooseCounty" (and you should set this as a parameter of type text to insure that it gets processed correctly).

2. the better meethod is to set the recordsource in the report's OnOpen event.

After you open the form as a dialog, you'd have something like this:

Me.Recordsource = "SELECT * FROM MyTable WHERE County='" _
  & Forms!ChooseCounty!ChooseCounty & "'"

And immediately after that line, you can close the form, since it's not needed any longer.

You will likely want an OnNoData event for the case where no records are returned. This is usually something simple like:

  MsgBox "No records found!"
  DoCmd.Close acReport, Me.Name

I hope this answers your questions, but if not, I'm happy to offer more explanation.