Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Passing criteria from a Form to Query (Access 2003)

    I would like to set up a blank form with comboboxes and have the values from them passed to a query.

    I set up the query qry_PlantSelector and based a form frm_SelectPlant on it.
    frm_SelectPlant displayed the records from qry_PlantSelector
    I linked up a couple of fields from the Form to the Query using the Criteria row of the query grid
    The Query ran as expected
    So far so good
    So I set up all the links and prettied up the formatting of the Form

    When I opened the form to check that it was functioning properly:
    1. It was blank (record 1 of 1 on the navigation bar)
    2. Although the comboboxes are populated they are 'locked' i.e. Although you can see the values you can't select them
    3. The query doesn't pull back any records at all

    Now I've obviously managed to do something - not sure what.

    How do I unlock the comboboxes? All of them are "Value Lists" i.e. Look Up values I typed in myself when I set up the underlying tables.

    Ideally I just want to use the Form to set parameters in the query. I don't want to display existing records and I don't want to create any new records based on the form.

    Many thanks
    Jill

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

    Re: Passing criteria from a Form to Query (Access 2003)

    If I understand your description correctly, your form should NOT be based on the query, i.e. the Record Source property of the form should be blank. The Control Source property of the combo boxes should also be blank.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing criteria from a Form to Query (Access 2003)

    Hi Hans

    Thanks yet again. Yes, the Form and Query are now working properly.

    However, I seem to lurch from one crisis to another...

    The query is returning only cases where the exact combination of parameters as set on the form is met.

    I would like the parameters to work independently of each other:

    For instance as well as comboboxes, I also have four checkboxes

    So say a red geranium is scented, a new variety and an award winner but not suitable for baskets. Now if the user checks all the correct boxes the query will return the red geranium. But if they check just scented it won't be. Now in my ideal world, I would like the user to be able to just check scented and for the query to return the red geranium.

    I have experimented with the 'or' criteria line in the query but can't even seem to get two checkboxes to work independently of each other let alone four.

    Hope this all makes sense

    Thanks very much
    Jill

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

    Re: Passing criteria from a Form to Query (Access 2003)

    Normally, a check box represents either Yes or No, i.e. scented or not scented, not both. But you can set the Triple State property of the check box to Yes (this property is in the Data tab of the Properties window.) This will add a neutral state to the check box.

    Let's say that the table your query is based on contains a field named Scented (a yes/no field), and that your form is named frmSelect and that it contains a check box named chkScented. You now probably have something like this in the criteria line of the query under Scented:
    <code>[Forms]![frmSelect]![chkScented]</code>
    Select this expression, and cut it to the clipboard (Ctrl+X).
    Paste it (Ctrl+V) into the Field row in the first empty column of the query design grid.
    Clear the View check box for this column.
    Enter the following in the criteria line for this column:
    <code>[Scented] Or Is Null</code>
    Repeat this for the other fields corresponding to check boxes (new, award, basket)

    Of course, you must substitute the actual names of your form, check boxes, and fields.

  5. #5
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing criteria from a Form to Query (Access 2003)

    Hi

    Managed (eventually) to get this to work. This was down to me in that in one of my experiments I set all the check box default values to =0. Having traced this, the checkboxes now work fine. Thank you very much.

    However... groan... Now that the checkboxes are okay I find I have a problem with the other combo box on the form. Again, I would like the combobox to work independently of the checkboxes. I wrote an IIf statement in the criteria box on the query:

    IIf([Forms]![frm_SelectPlantCharacteristic]![cboSpecial]=" ",[tbl_PlantCharacteristic]![Special],IIf(IsNull([Forms]![frm_SelectPlantCharacteristic]![cboSpecial]),[tbl_PlantCharacteristic]![Special],[Forms]![frm_SelectPlantCharacteristic]![cboSpecial]))

    This 'sort of works' but only if the tbl_PlantCharacteristic field is populated. If it's not then the query doesn't pull back any records.

    So, where am I going wrong this time please?

    Many thanks
    All the best
    Jill

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

    Re: Passing criteria from a Form to Query (Access 2003)

    I'm not sure I understand the situation. What is the column that has this condition?

  7. #7
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing criteria from a Form to Query (Access 2003)

    Hi

    Field: Special
    Table: PlantCharacteristic

    Thanks
    Jill

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

    Re: Passing criteria from a Form to Query (Access 2003)

    Try something similar to what I suggested for the check boxes:
    - Delete the condition for the Special field.
    - In the first empty column of the query grid, enter the following in the Field row:

    [Forms]![frm_SelectPlantCharacteristic]![cboSpecial]

    - Clear the Show check box for this column.
    - Enter the following in the Criteria line of this column:

    [Special] Or Is Null

  9. #9
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Passing criteria from a Form to Query (Access 2003)

    Hi Hans

    Thanks very much. Everything works fine now.

    All the best
    Jill

Posting Permissions

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