Search Function for Databases

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

My boss has once again tasked me with the not-too-enviable task of Access
programming. I've been asked to create a "search" function that basically
refreshes the page whenever a letter is entered, as opposed to using the
search bar at the bottom.

So, when you enter an, "S" in the bar at the top of the screen that I'll be
creating, it will refresh the page, moving you to the contacts with the last
name starting with "S".
 
Here's a snippet pulled from a search function I did not too long ago.

Private Function pfBuildSQL() As String
Dim pID As String
Dim pName As String
Dim pDescription As String
pID = Nz(Me.ctlID.Text, "")
pName = Nz(Me.ctlName.Text, "")
pDescription = Nz(Me.ctlDescription, "")
Ret = "SELECT * FROM tbloDocuments WHERE " _
& "(tbloDocuments.fldID Like '" & pID & "*') And " _
& "(tbloDocuments.fldName Like '*" & pName & "*') And " _
& "(tbloDocuments.fldDescription Like '*" & pDesc & "*')"
pfBuildSQL = Ret
End Function

There are three controls (actually quite a few more, but I reduced it to
three for this example) on the form, and each one of these has an OnChange
procedure with the following single line of code:

Me.ctlfrmDocuments_sList.Form.Recordsource = pfBuildSQL

(ctlfrmDocuments_sList is the name of the control of the datasheet subform
that lists the returned records)

With that line on the OnChange event of each field, every time the user
types in a character, the Recordsource gets recalculated.

For my purposes, the Document ID is a string (R0194R2), and the search only
returns documents that START with whatever they type (notice the lack of the
first wildcard "*" in the pID part of the sql), but if they type in the name
or description field it finds any matches, regardless of where in the field
it is located.

This should get you started...



--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Back
Top