470,612 Members | 2,251 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,612 developers. It's quick & easy.

Optional criteria in query

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?
Jun 30 '06 #1
1 3004
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
DoCmd.OpenQuery "Your Query Name Here"

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
Jul 13 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Woodies_46 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.