Allowing users to select multiple criteria from a combo box

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Hi! I'm working on a database right now that has a drop-
down menu of search terms. I originally had the menu in
three separate text boxes so the user could choose three
of the terms, but this makes searching difficult because
the user will have to search for a given term three
separate times, as results only come up for a specific
text box. What I would like to do is have one box of the
terms and then allow the user to select as many of those
terms as apply. In some databases you can do this by
pressing the control button down and then clicking on the
ones you want. Does Access allow something like that?
 
You could construct a parameter query, then use the query
as the record source for your form (or report?). The
resulting dialog boxes are not very flexible, so you could
use a form to select the criteria. Help contains pretty
good information on both of these procedures.
The approach I favor when using a form to specify criteria
is to use the original form's On Open event to open the
selection form. If your original form is frmMain, and
your selection form is frmSelect, the On Open event for
frmMain would include:
'Open the form that provides report parameters
DoCmd.OpenForm "frmSelect", , , , , acDialog

If frmSelect includes combo boxes for Name (cboName) and
Department (cboDept), the parameter query's criteria for
[Name] would be [Forms]![frmSelect]![cboName], and for
[Department] would be [Forms]![frmSelect]![cboDept]. If
the user is to be allowed to leave either field blank, add
Or Is Null to the criteria. The same system may be used
for additional criteria.
Pop Up and Modal properties for frmSelect should be set to
Yes. Once the user has selected the criteria, a command
button on frmSelect would have Me.Visible = False for its
On Click event. Similarly, a command button on frmMain
would have its On Click event set to:
Forms!frmSelect.Visible = True

Finally, the On Close event for frmMain would include:
DoCmd.Close acForm, "frmSelect"

Some people favor opening frmSelect first, and having a
command button on it to open frmMain. I find that
changing the visible property of frmSelect tends to be
smoother than opening and closing the form.
You can modify this approach to your needs. For instance,
if you don't want frmSelect to open right away, you could
open it from a command button (cmdOpenSelect) on frmMain.
Place cmdOpenSelect on top of the one described above for
making frmSelect visible, and include Me.Vislble = False
in the On Click event for cmdOpenSelect.
 
Back
Top