Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

VBA Recordset Filter
Recordset Filter






Visual Basic Tutorials:
Access-Google Earth
Age Calculation
Change To Proper Case
Email via Gmail #1
Send Gmail Email #2
Inactivity Logout Code
Outlook Email
Read Email Access
Email Attachment
Send Outlook Email
Running Sum
Concatenate Records
Stock Quotes
CreateQueryDef
Find Database Path
Detail-Master Update
Data Field Validation
Field Value New-Old
FindFirst
Access Version
Global Variable Parameter
Global Variables
Active Labels
Files List Box
Mail Merge
Quick Sort
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples


Access Visual Basic Recordsets

One of the most powerful features of Microsoft Access is the ability to work with your data using recordsets in VBA.   Beside updating, appending, and deleting records in recordsets you can also display the recordsets on an associated form or report.  In this example we will demonstrate how to dynamically filter the records. 

There are basically three techniques used to dynamically filter recordsets an Access Visual Basic where a continuous form is being used.

  • Filter recordset manually using the toolbar

  • Filter VB recordset using VBA with a text field or combo box

  • Use a parameter query that read the value of a text field or combo box to filter recordset

Our goal is to use the Store_Selection combo box to filter the records in the form.  These recordsets refer to sales and deliveries of bait to a store.  Here we will show you how to dynamically filter a continuous recordset in Visual Basic on a form.  Below is the form with which we will be working:


We use the Visual Basic AfterUpdate trigger on the property sheet of the combo box.  (note:  there is a similar subroutine for the Year field).  Here is the VBA code:

Private Sub Store_Selection_AfterUpdate()

Forms![$mainmenu]![F_History_By_Store_Final].Form.Filter = & _
"Store_ID=" & Me.Store_Selection.Column(0) & _
" and year=" & Chr(34) & Me.Year_Selection & Chr(34)

Form_F_History_By_Store_Final.Form.FilterOn=True

End Sub

Whenever the user selects a new store number the form immediately displays the new filtered recordset.  You can add text boxes and combo boxes to filter on any combination of fields in the recordset.  The Chr(34) is my way of putting in the double quote marks for character variables.










Blue Claw Database Design Downloadable Tutorial:
Programming Crosstab Query Example  








A Blue Claw Software Design Template:

Order Processing Case Study
 










A Blue Claw Database Design Article:

Cloud-Based Access Database
 

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016