Kim:
I'm just guessing here, but if your "search criteria" is being saved, you are
probably typing it into a bound column.
When we create a form that allows searching, we usually add a text box
control (or other type control) that is NOT bound to any table field. My form
might have fields like ...
FName LName Phone Address Title
These are all bound to the table and if you don't want them to be edited, you
can make them read-only in a couple different ways, but probably need to
set their Locked property to true.
Next, you add a text box that is NOT bound to the table. Name it txtNameFilter
Users may type whatever they want into this box and it's virtually thrown away
when the form is closed.
Finally, you add code to the AfterUpdate event of this box to change the form's
RecordSource property. If, for example, the form was based on tblPerson, the
after update event of txtNameFilter might look like this ...
Dim sRS as String
Dim sNameFilter as String
sNameFilter = Trim(Nz(Me!txtNameFilter, ""))
If sFilter = "" Then
sRS = "tblPerson"
Else
sRS = "SELECT * FROM tblPerson WHERE [FName] = '" & sNameFilter & "'"
End If
Me.RecordSource = sRs
Notice that where I inserted the filter criteria it's surrounded by single quotes.
If you do this with LName and the filter is O'Brian, that embedded quote will
give you trouble. In that case, you need to use the Replace function too ...
Replace(sNameFilter,"'","''")
Doubling the embedded single qoute solves the problem. This should get you
started, or at least help you know what to ask next.
but cannot find a solution. Is it