Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Combo Box to control Query (Access 2000 SR-1)

    I followed advice from this site to create an Unbound form with 4 combo boxes and a control button that opnes up a form based on the combo selections. All went fine except:

    1. I can select from 4 combo boxes and that feeds a query linked to a form
    STATE=California
    CITY=Berkeley
    BUILDING=home
    RENT=N

    But what if I just want to pick the first CombObox and then open the form. Let's say I just want California, then Go! or California,Berkeley then go!

    How do I set up my query to stop looking for Combo box 3 and 4 if I just want to select 1 and 2 and then open.

    I have a query that has: [Forms]![MasterMenu]![CatCombo01], ...CatCombo02, ...CatCombo03,...CatCombo04 in the criteria fields.

    Thanks

    Paul

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    Paul,

    Why not try using the * wildcard character?

    Maybe something like: IIf(IsNull([Forms]![MasterMenu]![CatCombo01]), Like "*", [Forms]![MasterMenu]![CatCombo01])

    Just a thought... Let me know if it works... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    When doing this in the past, I have set the default values of each combobox to *. Then have all your queries set us as Like(ComboboxName&'*'). Thus, if you choose California in the first combobox, the remainder of the queries will select all of the remaining data.

    HTH
    Regards,

    Gary
    (It's been a while!)

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    Thanks for clarifying that Gary! I knew it would be simple... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    Glad I could help. Hey, where is Hans and Charlotte. I much prefer reading there responses. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Regards,

    Gary
    (It's been a while!)

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> ...Hopefully taking a WELL EARNED break!!

    I know what you mean though... They're great... I read these posts day and night now...
    I think I'm hooked!!!! <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> Have a great evening!

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    <hr>I read these posts day and night now... I think I'm hooked!!!! <hr>
    Hehehehe. Then the plan is working! <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> <img src=/S/frank.gif border=0 alt=frank width=15 height=15>
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    I think I am missing somethiong here. It did not work. Are you saying the end result for the criteria will be *.* That did not work.

    In combo1 I selected 'California'
    In the rest, I placed the default to be *
    Is the query supposed to be:
    Combo1='California'
    Combo2=*.*
    Combo3=*.*
    Combo4=*.*

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    Try it like this: remove the existing criteria.

    In the first empty column of the query grid, enter [Forms]![MasterMenu]![CatCombo01] in the field row, and [State] Or Is Null in the criteria row.

    In the next empty column, enter [Forms]![MasterMenu]![CatCombo02] in the field row, and [City] Or Is Null in the criteria row.

    In the next empty column, enter [Forms]![MasterMenu]![CatCombo03] in the field row, and [Building] Or Is Null in the criteria row.

    In the next empty column, enter [Forms]![MasterMenu]![CatCombo04] in the field row, and [Rent] Or Is Null in the criteria row.

    Clear the check boxes to display these "fields".

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Using Combo Box to control Query (Access 2000 SR-1)

    -First, go to the form where you are making selections and go to design mode. Set the default to each combobox to "*". When you open the form, each combobox will show an "*" as the selection.

    - Now go to the query driving the second form. In the criteria that links to the data selected on the first form, change each from [Forms]![MasterMenu]![CatCombo01] to
    Like ([Forms]![MasterMenu]![CatCombo01]&'*')


    Save the form and query.

    When you select California and then launch the second form, the query will say, get all records where Combo1 like California* and all other records for Combo 2, 3, and 4.

    HTH.

    Or, try Hans idea.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •