Set a control to empty or null

[Guest]

1) I have a query on my form where one of the combo boxes is set as a
condition on one of the queries fields. In effect this allows to filter
records.

2) I have an iif statement in this condition iif(myCombo is null, my Field,
mycombo). In affect this will allow all records to be shown when the form is
opened. Once an option is selected from the combo it requeries and filter
correctly.

At this point I want to reset the combos so that they are empty. In other
words display all records.

I have tried the following code on the combo but it does not seem to work.


[Forms]![myform]![myControl] = Null

or

[Forms]![myform]![myControl] = "*"

Is Null not the same as empty. What am I missing

Bruce

 

[Allen Browne]

Hi Bruce.

A simple alternative to achieve what you want is to remove the combo box
reference from the query, and use the Filter of the form instead.

You already have an *unbound* combo on your form where the user can enter
the limiting value? In its AfterUpdate event procedure,

Private Sub MyCombo_AfterUpdate()
If Me.Dirty Then 'Save before filter
Me.Dirty = False
End If
If IsNull(Me.MyCombo Then
Me.FilterOn = False 'show all records
Else
Me.Filter = "[MyField] = " & Me.MyCombo
Me.FilterOn = True
End If
End Sub

Note: if MyField is a Text field (not a Number field), you need extra
quotes:
Me.Filter = "[MyField] = """ & Me.MyCombo & """"