title: Access 2007: Filtering a report's results using a drop-down box
body: <p>My question is twofold. </p>
  
  <ul>
  <li>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. </li>
  </ul>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>I may be halfway there with the following: </p>
  
  <hr>
  
  <p>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'.</p>
  
  <p>Add a Command Button to the form.
  Code the button's click event as follows:</p>
  
  <p>(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:</p>
  
  <p>[Event Procedure]</p>
  
  <p>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.)</p>
  
  <p>Me.Visible = False</p>
  
  <p>Close the Code window.</p>
  
  <p>Name this form 'ChooseCounty'.</p>
  
  <p>In the Query that is the Report's Record Source [County] field
  criteria line, write:</p>
  
  <p>forms!ChooseCounty!ChooseCounty</p>
  
  <p>Next, code the Report's Open event:
  (Using the same method as described above)</p>
  
  <p>DoCmd.OpenForm "ChooseCounty", , , , , acDialog</p>
  
  <p>Code the report's Close event:</p>
  
  <p>DoCmd.Close acForm, "ChooseCounty"</p>
  
  <p>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.</p>
  
  <hr>
  
  <p>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? </p>
  
  <ul>
  <li>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!</li>
  </ul>
  
  <p>~ T</p>