"All" option in data filtering

[Dan Neely]

I'm using the selection in a combobox showing all the values in a
lookup table to filter records in a child form. I did this by linking
the value of the combobox to the equivalent field in the records being
shown in the child form. I've gotten a feature request to add an
"All" option to the available selections. Is this doable, and if so
how?

 

[Steve]

Your form is based on a query and one of the fields has criteria that looks
like:
Forms!NameOfYourForm!NameOfYourCombobox
Change that criteria to:
Forms!NameOfYourForm!NameOfYourCombobox Or
(Forms!NameOfYourForm!NameOfYourCombobox Is Null)

Tell your users to not make a selection in the combobox and all records will
be displayed in your form.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[SusanV]

That's just silly - "tell your users" - um... no thanks.

Dan, try this link, it should show you how to add the ALL option:
http://support.microsoft.com/kb/210290

 

[Dan Neely]

Your form is based on a query and one of the fields has criteria that looks
like:
Forms!NameOfYourForm!NameOfYourCombobox
Change that criteria to:
Forms!NameOfYourForm!NameOfYourCombobox Or
(Forms!NameOfYourForm!NameOfYourCombobox Is Null)

The way I have it currently configured I'm not using any criteria in
my data query's.

My toplevel form is bound to the lnkFoo table, but doesn't display any
records from it directly. It has an unbound combobox ComboBar with
lkupBar as a rowsource. BarID is an FK in the lnkFoo table. Also in
the toplevel form is a subform control. It links BarID between the
child and master forms.

The child form inside the subform control has a query that gets the
contents of lnkFoo and several fields from additional tables via a
different FK in the table.

I'm applying the filter in the combobox AfterUpdate event.

The relevant snippet of code from the event is...

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[BarID] = " & Str(Nz(Me![ComboBar], 0)) & " AND
[BazID] = " & Str(Nz(Me![ComboBaz], 0))
'filter is set to match nothing. This is needed because if
there's no match the bookmark
'is set to both FKs = 1. I wasn't able to change it directly.
If rs.NoMatch Then
Me.ChildForm.Form.FilterOn = True
Else
Me.ChildForm.Form.FilterOn = False
Me.Bookmark = rs.Bookmark
End If

BazID and ComboBaz are a second FK and combo that are also being used
for filtering purposes.

I tried changing the FindFirst code to this, but if I backspace out
the selection in ComboBar the child form doesn't update the displayed
records.

rs.FindFirst "[BarID] = " & Str(Nz(Me![ComboBar], 0)) & "OR [BarID] =
NULL" & " AND [BazID] = " & Str(Nz(Me![ComboBaz], 0))

 

[Douglas J. Steele]

Actually, instructing the users to not select something from a specific
combo box if they don't want to filter on that list does make sense: put a
label on the form that says that if you don't want to have to talk to them.
The problem, though, is that once they've selected a value from the combo
box, they can't restore it to an unselected state unless you add a button
that assigns Null to the combo box.

While the link you showed explains how to add an ALL selection to the combo
box, it doesn't explain how you'd use it. If you strictly leave the criteria
as Forms!NameOfYourForm!NameOfYourCombobox, it's going to look for fields
that have "ALL" as a value, which isn't what's wanted. You need to change
the criteria to

Forms!NameOfYourForm!NameOfYourCombobox OR
(Forms!NameOfYourForm!NameOfYourCombobox = "All")

 

[Douglas J. Steele]

The way I have it currently configured I'm not using any criteria in
my data query's.

My toplevel form is bound to the lnkFoo table, but doesn't display any
records from it directly. It has an unbound combobox ComboBar with
lkupBar as a rowsource. BarID is an FK in the lnkFoo table. Also in
the toplevel form is a subform control. It links BarID between the
child and master forms.

The child form inside the subform control has a query that gets the
contents of lnkFoo and several fields from additional tables via a
different FK in the table.

I'm applying the filter in the combobox AfterUpdate event.

The relevant snippet of code from the event is...

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[BarID] = " & Str(Nz(Me![ComboBar], 0)) & " AND
[BazID] = " & Str(Nz(Me![ComboBaz], 0))
'filter is set to match nothing. This is needed because if
there's no match the bookmark
'is set to both FKs = 1. I wasn't able to change it directly.
If rs.NoMatch Then
Me.ChildForm.Form.FilterOn = True
Else
Me.ChildForm.Form.FilterOn = False
Me.Bookmark = rs.Bookmark
End If

BazID and ComboBaz are a second FK and combo that are also being used
for filtering purposes.

I tried changing the FindFirst code to this, but if I backspace out
the selection in ComboBar the child form doesn't update the displayed
records.

rs.FindFirst "[BarID] = " & Str(Nz(Me![ComboBar], 0)) & "OR [BarID] =
NULL" & " AND [BazID] = " & Str(Nz(Me![ComboBaz], 0))

If you're doing it in VBA, you'd strictly skip the logic if nothing was
selected:

Dim rs As Object

Set rs = Me.Recordset.Clone
If IsNull(Me![ComboBar]) = False Then
rs.FindFirst "[BarID] = " & Str(Nz(Me![ComboBar], 0)) & _
" AND [BazID] = " & Str(Nz(Me![ComboBaz], 0))
If rs.NoMatch Then
Me.ChildForm.Form.FilterOn = False
Me.Bookmark = rs.Bookmark
Else
Me.ChildForm.Form.FilterOn = True
End If
End If

(your choices for what to do on a match or a no match seemed backwards to
me, so I reversed them)

 

[Dan Neely]

That's just silly - "tell your users" - um... no thanks.

Dan, try this link, it should show you how to add the ALL option:http://support.microsoft.com/kb/210290

I'm not sure if something changed between access2000 and 2003, but
when I follow the instructions provided I get compile errors if i
remove all the underscores from the pasted code. It compiles fine if
I leave them in, but when I follow the instructions about how to add
it to the combobox in the northwindDB, I get a cb with no entries in
the dropdown when I view the form. A breakpoint set on on the
function header of AddAllToList() never fires when the form is shown.