combo box, select all

[NukeEng85]

I have multiple combo boxes that I'm using as filters for a subform. I have
two questions:

1) I would like to have an option to select "all" in one of the combo box,
and it would effectively turn off the filter on that combo box, and only the
other combo box would be used in the filter

2)Right now, to get the information on in the subform to show up, I have to
save the form, click into Design view, and then click back into viewing the
form for it to reset. Is there a way to link a macro to a button to update
the form? If so, what would the code be?

 

[Wayne-I-M]

Try this link

http://support.microsoft.com/?id=210290

 

[Ken Sheridan]

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

 

[NukeEng85]

Do you know what code I would use for the Go button? I found this online,
but I don't know enough about coding to know what it all means or what I need
to change in it to make it work for my specific headings and cbo. When I
tried it as it is, nothing happened

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

 

[Ken Sheridan]

The code you cited is only appropriate if you are setting the form's Filter
property. Also it applies to the parent form (that's what Me refers to) not
the subform, which is why nothing happens. However, from the behaviour you
described in your original post it sounds to me like you are not filtering
the subform in the true sense, but restricting the subform's underlying
recordset by means of parameters in its RecordSource query which reference
the combo box controls on the parent form, in which case all you'd need in
the button's Click event procedure is to requery the subform control as I
described with:

Me.sfcMySubform.Requery

bearing in mind what I said about sfcMySubform being the name of the subform
control not its underlying form object.

You'll appreciate that to a large degree I'm having to second guess how your
form/subform is working because you haven't given us a lot of information
about how things are set up.

Ken Sheridan
Stafford, England