RE: combo box, select all
- From: NukeEng85 <NukeEng85@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 6 Aug 2008 11:20:01 -0700
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" wrote:
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" wrote:
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?
- Follow-Ups:
- RE: combo box, select all
- From: Ken Sheridan
- RE: combo box, select all
- References:
- combo box, select all
- From: NukeEng85
- RE: combo box, select all
- From: Ken Sheridan
- combo box, select all
- Prev by Date: RE: Northwind Sample Template Example
- Next by Date: Data Access Page - Access 2003
- Previous by thread: RE: combo box, select all
- Next by thread: RE: combo box, select all
- Index(es):
Relevant Pages
|
