Query criteria from form combobox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good afternoon,
I'm preparing a query that has a particular criterion in the DEPARTMENT
field. Rather than rewriting the query (and the 60 others that are based on
it) for the ability to leave DEPARTMENT unfiltered or filtered, I'd like to
tie the criterion to a combobox that may have a value in it on a form that
may be open. (does THIS make sense?)

First, I want the query field to check if the form is open. I recall in
prior versions that there was a way (not that I can recall how!) to use a
domain aggregate function to see if the objectname (in this case "frmdept")
was in a particular system object table. Anyway, if the form is open, I want
to then check to see if the value of my combobox (cmbdept) is either CNP or
RX. If the conditions a) form open and b) not null (either CNP or RX are
selected), I'd like the criterion to be "CNP" or "RX".

If this continues to make sense and interests someone enough to assist, I
will be very much appreciative. Thank you very much in advance!
 
Making the query get criteria from a combobox is simple.

SELECT ...
FROM MyTable
WHERE Department=Forms![OPEN_Form_Name]![cmbDept]

The query cannot check if the form is open. You need to use VBA for
that.
If IsLoaded("frmDepartment") ...

If you open a form or report bound to MyTable, then you can pass
filters in the Open event of the form/report.
 
If in the combo box has "BOTH" , "CNP" or "RX", how can we display both
record if neither CNP or RX get selected?
 
Thanks. I had successfully gotten the query to use the combobox as
criterion. I'm not familiar with the isloaded. I'm guessing I need to make
a function for my query criterion.
 
Back
Top