Optional criteria in query

[lucazz]

I have a form with 2 comboboxes (dept, section) and a listbox (employees).
Listbox is based on a query with 2 criteria (Forms!Form!Dept and Forms!Form!Section). Whenever I choose values in both comboboxes listbox works fine. But how can I make section criteria optional i.e. when choosing dept I would see all the dept's employees, and when I need to choose section the list would get shrinked to just the section's employees.
I've tried (according to MSKB) Forms!Form!Section Or Forms!Form!Section Is Null - but when I save the query Forms!Form!Section Is Null disapperars...
Am I doing something wrong? I'm working on Acc2000 - any suggestions?

[zzqxxq]

Here's how I would handle it

I would make a new query based only on the department combo box - then when a user selects a department all the employees in that department would fill the listbox - just like you want to do

then use the original query with the section box so that if a user selected a section AFTER selecting department he would get the list box of just that sections' employees - like you get now.

To prevent the section combo box from being selected first, do a little test using the onchange function -

sub SectionComboBox_OnChange

If Len([ListBoxName]) < 1 then
MsgBox "You must select a department before selecting a section", vbOkOnly
SectionComboBox.Value = 0
Else
DoCmd.OpenQuery "Your Query Name Here"
Endif

End Sub

if the list box is empty and the user tries to change the section combo box -it display a message box saying you must select a department first - with an ok button then sets the section button back to no selection (0) - otherwise it does the section query (the one you have now) using the selected department and section.

Write if you need more help