You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
(Guest blogger, Molly Pell is a Senior Systems Analyst at FMS, Inc., a leading Access applications company, which designs custom solutions to improve operations for small and large organizations in all sectors.)
Here's a neat trick that you can use to filter a Continuous or Split form while your users are typing in a Combo Box. As the user types, the form filter updates to display full or partial matches for the value entered.
This example uses a slightly modified version of the Northwind 2007 Template, available for download from Microsoft. The "Customer List" form is a Split form that displays a list of all customers. We want to allow users to filter this list to easily find a customer, even without knowing the full customer name.
To do this, we add a Combo Box named cboFilter, with the following notable properties:
Here's what our form looks like:
Then in the Change event of the Combo Box, we add the following code:
Private Sub cboFilter_Change() ' If the combo box is cleared, clear the form filter. If Nz(Me.cboFilter.Text) = "" Then Me.Form.Filter = "" Me.FilterOn = False ' If a combo box item is selected, filter for an exact match. ' Use the ListIndex property to check if the value is an item in the list. ElseIf Me.cboFilter.ListIndex <> -1 Then Me.Form.Filter = "[Company] = '" & _ Replace(Me.cboFilter.Text, "'", "''") & "'" Me.FilterOn = True ' If a partial value is typed, filter for a partial company name match. Else Me.Form.Filter = "[Company] Like '*" & _ Replace(Me.cboFilter.Text, "'", "''") & "*'" Me.FilterOn = True End If ' Move the cursor to the end of the combo box. Me.cboFilter.SetFocus Me.cboFilter.SelStart = Len(Me.cboFilter.Text) End Sub
Now as the user types, the customer list automatically updates. If the user types an exact match, the list is filtered for an exact match. If the user types a partial match, the list is filtered for any company whose name contains the string. If the user clears the combo box, the filter is cleared.
Note that this same thing can be done with a text box, but using a combo box adds the extra perk of making all values available in the drop down.
For more combo box tips, check out our Top 5 Combo Box Tips for Access at FMS, Inc.
Thanks Molly, I used it today on an application with a fwe changes for filtering nummeric values and dates ! Works fine !
in Private Sub cboFilter_Change() why do you use the Replace function to replace single quotes with double quotes?
Thanks for reading. To include single quotation marks in the filter Value, you need to use two single quotation marks to represent one. For example, to filter on O'Malley, the criteria string should be "= 'O''Malley'".
See msdn.microsoft.com/.../ms676691%28v=vs.85%29.aspx for additional details.