When I create a filter in VBA, I can never remember when to use quotes, pound signs or when to concatenate. Is there an easier way to build the filter?
	How can I open a form to display only those records related to the main form?
	I have an unbound textbox on a bound form. I want users to be able to type in a client's name and then have only those records that match, appear in the form. How can I do this?

When I create a filter in VBA, I can never remember when to use quotes, pound signs or when to concatenate. Is there an easier way to build the filter?
Keywords: Filter VBA
Posted Apr 24, 1997

Access 95 provides a new method, BuildCriteria. This accepts 3 arguments; Field Name, Field Type and value to test. The nice perk about this method is that by specifying the Field Type (a VBA intrinsic constant) the method does all the work for you:

	Dim szCrit As String
	
	szCrit = BuildCriteria("OrderDate", dbDate, ">1-1-95 and <5-1-95")

This example returns the following criteria string:

	OrderDate>#1/1/95# And OrderDate<#5/1/95#

As you can see by using the VBA constant, dbDate, the method supplies the necessary pound signs.

How can I open a form to display only those records related to the main form?
Keywords: Open Form Record
Posted Apr 24, 1997

There are several ways to do this. The most straightforward way is to open the form using the OpenForm method of the DoCmd object. You can pass a "Where" condition when you open the form using this method:

	DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

Another way you could do this:

	''' Build your filter
	szCrit = BuildCriteria("LastName", dbText, "King")

	''' Set the forms filter property
        Form_Employees.Filter=szCrit

        ''' Make the form visible
	Form_Employees.Visible=True

I have an unbound textbox on a bound form. I want users to be able to type in a client's name and then have only those records that match, appear in the form. How can I do this?
Keywords: Unbound Textbox Record
Posted Apr 24, 1994

An easy way to do this is to use the ApplyFilter method of the DoCmd object. All you have to do is write a little procedure to run in the unbound textbox's exit event:

	Private Sub LastName_Exit(Cancel As Integer)
		
	Dim szLastName As String, szCrit As String

	''' Retrieve user entered search criteria
        szLastName = Nz(Me!LastName,"")

	''' Determine if user entered criteria
        If Len(szLastName)<1 Then
	     MsgBox "You must first enter a last name to search on."
	     Cancel = True
	Else
	     ''' Build criteria
	     szCrit = BuildCriteria("LastName", dbText, szLastName)

	     ''' Apply the filter to the form
	     DoCmd.ApplyFilter , szCrit
	End Sub

Back to Top

Back to the FAQ Table of Contents

Life does not begin at conception, but when the kids leave home and the dog dies.