Optional criteria in query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • lucazz
    New Member
    • Jun 2006
    • 2

    #1

    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!Dep t and Forms!Form!Sect ion). 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!Sect ion Or Forms!Form!Sect ion Is Null - but when I save the query Forms!Form!Sect ion Is Null disapperars...
    Am I doing something wrong? I'm working on Acc2000 - any suggestions?
  • zzqxxq
    New Member
    • Jul 2006
    • 28

    #2
    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

    Comment

    Working...