Need help designing form in Access 2003 that searches for value

[Derek Jones]

I am currently working on an application for our Business Office to easily
search for new budget codes that we have to establish due to an
administrative system conversion we are currently undergoing. They would
like to be able to find a new code via searching by old code or searching by
the object name. I have the search by old code form done exactly the way
they want it. For the search by object name form, I have it set up with a
combo box and a subform with three fields (old code, name, new code). I set
it up so you can select a name from the combo box and it would automatically
display only the records with that name in the subform. However, the names I
am working with are rather long, and the department I'm creating this
application for would like to be able to type in a portion of the name and be
able to see all records that contain the text that they enter (regardless of
where the text is located in the field). I've tried using the command button
wizard, but it's no help. I've tried creating macros, but was unsuccessful
there as well. Can someone out there offer me any suggestions? Thanks in
advance.

Derek Jones
Software Support Specialist
Shenandoah University
Winchester, VA

 

[Ken Ismert]

Derek,

Some filter code should get you started. Put this routine in the form's
module:

Public Sub FilterName(vName As Variant)

Dim sFilter As String
Dim sName As String

On Error GoTo HandleErr

' Turn off filter if Null
If IsNull(vName) Then
Me.FilterOn = False
Exit Sub
End If
' Parse Name
sName = CStr(Nz(vName, ""))
sName = Replace(sName, "'", "''")
If Right(sName, 1) <> "*" Then
sName = sName & "*"
End If
' Build filter - use Like comparison
sFilter = BuildCriteria("[Name]", dbText, "Like '" & sName & "'")
Me.Filter = sFilter

Exit Sub

HandleErr:
Err.Raise Number:=Err.Number, _
Description:=Err.Description, _
Source:="Form_MyForm.FilterName" & vbCr & Err.Source
Exit Sub
End Sub

To use:

FilterName cboSearch.Value - Filter for combo value
FilterName Null - Clear filter

-Ken