checkbox as query criteria

[remigio]

Hi,
in an Access form i've put a combo box that has a query as data
source. On this form I'd like to add a check box that, when it is
selected, adds the "is not null" criteria on a query field.
There is a simple way to do this?
Thank you very much for help.

--

Remigio

www.sacraspina.it
www.amicitosondoro.it
www.icmonteodorisio.it
www.parrocchiacupello.it
www.cralnuovainiziativa.it
www.associazionehistonium.it

 

[Tom van Stiphout]

On Sat, 1 May 2010 05:55:14 -0700 (PDT), remigio <[email protected]>
wrote:

Create two queries. Then switch between them based on your needs. For
example in the myCheckBox_AfterUpdate event you could write:
if me.myCheckbox.value = True then
Me.myCombobox.RowSource = "query2"
else
Me.myCombobox.RowSource = "query1"
end if
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[remigio]

Create two queries. Then switch between them based on your needs. For
example in the myCheckBox_AfterUpdate event you could write:
if me.myCheckbox.value = True then
  Me.myCombobox.RowSource = "query2"
else
  Me.myCombobox.RowSource = "query1"
end if
(of course you replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

Thank you very much.

--

Remigio

www.sacraspina.it
www.amicitosondoro.it
www.icmonteodorisio.it
www.parrocchiacupello.it
www.cralnuovainiziativa.it
www.associazionehistonium.it

 

[remigio]

Remigio:

You can in fact do it with a single query as the RowSource of the combo box,
e.g.

SELECT SomeField
FROM SomeTable
WHERE Form!YourCheckBox = FALSE
OR (Form!YourCheckBox = TRUE
AND SomeOtherField IS NOT NULL)
ORDER BY SomeField;

Note the use of the Form property to reference the current form rather than
referencing it as a member of the Forms collection.

This would return all rows from the table if the check box is not checked,
and only those where SomeOtherField contains a value if the check box is
checked.  Be sure that the check box's default value is False by putting:

Me.YourCheckBox = False

in the form's Open event procedure.  Otherwise it will be Null when theform
opens, until checked by the user.  In the checkbox's AfterUpdate event
procedure requery the combo box:

Me.YourComboBox.Requery

Ken Sheridan
Stafford, England

Thank you so much!