Microsoft Office Tutorials and References
In Depth Information
Selecting and Filtering with Cascading Combo Boxes
7. Beside the unchecked Show checkbox and the reference to the form
field, note that the GroupBy button has been pressed and that a Total
row is now present. The GroupBy suppresses duplicate rows (the way
Distinct did for us in the previous combo box), but also offers the
possibility to perform statistics.
It is a good idea to show the user how many LastNames there are. The
field [ID] has been added and instead of grouping on this field, the
Count option has been set. Now every name is unique, but there will
also be a count showing the user how many rows are displayed when
selected. Just try the combo box cmbFirstName1 for the name Carrie to
see the result.
8. Finally, ensure that after selecting the LastName, the filtered row(s)
are made visible in the datasheet subform. Use the After Update event
of the cmbLastName. After every change, the following code is
' Now that First and lastname have been selected¶
' apply a filter to the subform to show them¶
' 1 - fill filter¶
Me.sfrmName.Form.Filter = [LastName]= & Chr(34) & Me.cmbLastName1 &
Chr(34) & AND [FirstName]= & Chr(34) & Me.cmbFirstname1 & Chr(34)¶
' 2 - apply filter by making it active¶
Me.sfrmName.Form.FilterOn = True¶
The interesting part of this bit of code is the use of CHR(34). Using a string in a
WHERE clause requires the embedding of it in single quotes. The problem with
names, however, is that they can contain a single quote. When this happens,
Access sees the string as complete, and sees the remainder of the name as a
command. The CHR(34) is another way to create a double quote. Embedding
the name within double quotes ensures that a single quote does not break up
the created string.
Sample with single quote name in a query:
SELECT LastName FROM tblName WHERE FirstName = 'O'Conner'¶
Sample with the CHR(34) and the same name:
SELECT LastName FROM tblName WHERE FirstName = O'Conner¶
Search JabSto ::

Custom Search