1..A simple way to show <All> or similar in a combo box's list is to use a
UNION operation e.g. by setting the control's RowSource to:
SELECT LastName, 1 AS SortColumn
FROM Contacts
UNION
SELECT "<All>", 0
FROM Contacts
ORDER BY SortColumn, LastName;
or if the bound column is a hidden numeric key,
SELECT ContactID, Lastname, FirstName,
FirstName & " " & LastName, 1 AS SortColumn
FROM Contacts
UNION
SELECT NULL, NULL, NULL,"<All>", 0
FROM Contacts
ORDER BY SortColumn, LastName, Firstname;
In which case you'd hide the first three columns by setting the ColumnWidths
to something like the following, which would list the contacts in the format
Ken Sheridan, ordered by last name then first name:
0cm;0cm;0cm,8cm,0cm
and the ColumnCount property to 5.
To test for the <All> row you'd examine the control's value for NULL.
2. It depends on whether you are really 'filtering' the subform, i.e.
setting its Filter and FilterOn properties, or (more likely I'd guess)
'restricting' its underlying recordset by referencing the parent form's
control's as parameters. Assuming the latter you need to requery the subform
with:
Me.sfcMySubform.Requery
where sfcMySubform is the name of the subform control, i.e. the control in
the parent form's Controls collection which houses the subform, not the name
of its underlying form object, unless of course both have the same name. The
code is called from within the parent form's Module, e.g. in the Click event
procedure of a 'Go' button. If you are unfamiliar with entering VBA code in
event procedures this is how its done:
Select the button in form design view and open its properties sheet if its
not already open. Then select the On Click event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the line of code between these two existing lines.
In the subform's RecordSource query to allow for the <All> selection you
need to test the parameter for, in the case of the first simple combo box
above, OR <parameter> = "<All>". In the case of the second more complex one
you test for OR <parameter> IS NULL. So for the first scenario, lets assume
you also have a cboCityID combo box on the parent form, so you want to be
able to restrict the subform to a particular last named contact(s) in the
selected city or to all contacts in the selected city, then the query's WHERE
clause would be like this:
WHERE (LastName = Forms!MyForm!cboLastName
OR Forms!MyForm!cboLastName = "<All>")
AND CityID = Forms!MyForm!cboCityID
where 'MyForm' is the name of the parent form. The parentheses are
important here as the Boolean OR operation has to be evaluated independently
of the AND operation. In query design view you'd enter (as a single line):
LastName = Forms!MyForm!cboLastName OR Forms!MyForm!cboLastName = "<All>"
in the first 'criteria' row of the Lastname column and:
Forms!MyForm!cboCityID
in the first 'criteria' row of the CityID column.
If you do it in query design view, however, and then save the query, when
you open it again in design view Access will have moved things around. Don't
worry, it will work in exactly the same way, but the underlying logic will be
less clear than if you enter and save it in SQL view.
In the second scenario it would be:
WHERE (ContactID = Forms!MyForm!cboContactID
OR Forms!MyForm!cboContactID IS NULL)
AND CityID = Forms!MyForm!cboCityID
Note that you don't test for = NULL. Nothing equals NULL, not even NULL, so
you must use IS NULL.
Ken Sheridan
Stafford, England
