"Show All" in combo box not working

[Jimmy]

I use a combo box to filter the results of a form.

Sql for combo box is...
SELECT StatusPriority, Status FROM tblStatus UNION Select = 0 as AllChoice,
"Show All" as Bogus From tblStatus ORDER BY StatusPriority;

AfterUpdate code from combo box is...
If Me![Status] = 0 Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[Status]='" & [StatusFilterCombo] & "'"
End If

All of the normal entries for the combo work fine in filtering the results
but when I select "Show All" no records show up. It is as though access
skips the If part of the statement and goes right to the then, finds no
records with "0" in [Status]

I tried using null values instead... UNION Select Null as AllChoice and If
IsNull (Me![Status]) Then and had the same results.

What am I doing wrong?

[Tom van Stiphout]

On Mon, 23 Oct 2006 01:28:04 GMT, "Jimmy" <no**@none.comwrote:

Don't guess what Access is doing. Put a breakpoint on the If line, and
step through the code.

Personally I would write:
Me.FilterOn = False

-Tom.

>I use a combo box to filter the results of a form.

Sql for combo box is...
SELECT StatusPriority, Status FROM tblStatus UNION Select = 0 as AllChoice,
"Show All" as Bogus From tblStatus ORDER BY StatusPriority;

AfterUpdate code from combo box is...
If Me![Status] = 0 Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[Status]='" & [StatusFilterCombo] & "'"
End If

All of the normal entries for the combo work fine in filtering the results
but when I select "Show All" no records show up. It is as though access
skips the If part of the statement and goes right to the then, finds no
records with "0" in [Status]

I tried using null values instead... UNION Select Null as AllChoice and If
IsNull (Me![Status]) Then and had the same results.

What am I doing wrong?

[Jimmy]

Me.FilterOn=False did nothing by simply replacing it for the
DoCmd.ApplyFilter line. No surprise there.

As for the breakpoints, Access is certainly not doing what I expected.
Whenever I make a selection in the combo box with a breakpoint on the if
line, Me![Status] Access shows its value as being whatever the last
successful value was. For example, if, without using the breakpoint I open
the form and select "Completed" from the list, which has an id number of 4,
Access shows Me!Status = 4. When I continue in the code I get an error
(2488) that I cant use applyfilter in this window. That doesn't make sense
since without the breakpoint the filters work fine.

If for instance, I remove the breakpoint and run the form. In the combo box
I select "Show All". All of the records disapear from the form. I go to the
code window and insert breakpoint on the if line and go back to the form and
attempt to select anything from the combo box, when I am taken back to the
code Me!Status = Null !!! What gives.

Don't guess what Access is doing. Put a breakpoint on the If line, and
step through the code.

Personally I would write:
Me.FilterOn = False

-Tom.

[arelby]

How about

If Me![StatusFilterCombo] = 0 ?

Richard Bernstein

Jimmy wrote:

I use a combo box to filter the results of a form.

Sql for combo box is...
SELECT StatusPriority, Status FROM tblStatus UNION Select = 0 as AllChoice,
"Show All" as Bogus From tblStatus ORDER BY StatusPriority;

AfterUpdate code from combo box is...
If Me![Status] = 0 Then
DoCmd.ShowAllRecords
Else
DoCmd.ApplyFilter , "[Status]='" & [StatusFilterCombo] & "'"
End If
<snipped>
What am I doing wrong?