Search button on form - Help w/ code

[DC]

I need help. I've confused myself with all of the things I've tried.
I'm trying to modify the exiting Contacts template in Access 2000 to
include a search option. To the Contact Entry form I've added an
unbound text field with lable and a command button. The following code
is in the On Click event of the Command button.

Private Sub Button197_Click ()
On Error GoTo Err_Button197_C lick

Dim strWhere As String
If Not IsNull(Me.TxtSe arch) Then
strWhere = "LastName = " & Me.TxtSearch
End If
DoCmd.OpenForm "Contacts", , , strWhere
rem Err_Button197_C lick:
rem MsgBox "Error finding record: " & Error$
Rem RetVal = False
Rem Resume Next
Rem MsgBox Error$
Rem Resume Exit_Button197_ Click

Exit_Button197_ Click:
Exit Sub

End Sub

When I enter the name and click, I get a popup titled "Enter Parameter
Value" with an entry field entry field. If I enter the text in this
field, the form shows the record I'm looking for. So, it works, sort
of. Why am I getting the popup? How do I get rid of it?

Also, I'd like to have the other records available. Right now, it
just pulls up the record I'm looking for. To view the other records I
have to click the Remove Filter button on the toolbar.

While you're educating me, I don't really understand what the &
Me.TxtSearch part of the where statement does.

Thanks in advance for any help.

Dave

[Allen Browne]

You need extra quotes:
strWhere = "LastName = """ & Me.TxtSearch & """"

Explanation: You're after a string such as:
LastName = "Smith"
but this whole thing is in quotes. You can't have:
"LastName = "Smith""
because when VBA reaches the quote before Smith, it thinks the quote is
ended, and doesn't know what to do with the rest of the line. To convention
is to double-up the quotes when they are embedded, e.g.:
"This string has a ""word"" in quotes"

So, you need:
"LastName = ""Smith"""
Hence:
"LastName = """ & Me.TxtSearch & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DC" <ds*****@hotmai l.com> wrote in message
news:d2******** *************** ***@posting.goo gle.com...

I need help. I've confused myself with all of the things I've tried.
I'm trying to modify the exiting Contacts template in Access 2000 to
include a search option. To the Contact Entry form I've added an
unbound text field with lable and a command button. The following code
is in the On Click event of the Command button.

Private Sub Button197_Click ()
On Error GoTo Err_Button197_C lick

Dim strWhere As String
If Not IsNull(Me.TxtSe arch) Then
strWhere = "LastName = " & Me.TxtSearch
End If
DoCmd.OpenForm "Contacts", , , strWhere
rem Err_Button197_C lick:
rem MsgBox "Error finding record: " & Error$
Rem RetVal = False
Rem Resume Next
Rem MsgBox Error$
Rem Resume Exit_Button197_ Click

Exit_Button197_ Click:
Exit Sub

End Sub

When I enter the name and click, I get a popup titled "Enter Parameter
Value" with an entry field entry field. If I enter the text in this
field, the form shows the record I'm looking for. So, it works, sort
of. Why am I getting the popup? How do I get rid of it?

Also, I'd like to have the other records available. Right now, it
just pulls up the record I'm looking for. To view the other records I
have to click the Remove Filter button on the toolbar.

While you're educating me, I don't really understand what the &
Me.TxtSearch part of the where statement does.

Thanks in advance for any help.

Dave

[DC]

Thanks Allen. Any suggestions on how to get all of the records to be
available in the search results form? Right now it just opens a form
with the desired record, but I have to "remove filter" to get to the
other forms.

Dave

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message news:<41******* *************** @per-qv1-newsreader-01.iinet.net.au >...

You need extra quotes:
strWhere = "LastName = """ & Me.TxtSearch & """"

[Allen Browne]

If you want to open the form unfiltered, just use:
DoCmd.OpenForm "Contacts"

Presumably you want to then show the first matching record:
Dim frm As Form
Set frm = Forms("Contacts ")
With frm.RecordsetCl one
.findFirst strWhere
If .NoMatch Then
MsgBox "Not found"
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"DC" <ds*****@hotmai l.com> wrote in message
news:d2******** *************** ***@posting.goo gle.com...

Thanks Allen. Any suggestions on how to get all of the records to be
available in the search results form? Right now it just opens a form
with the desired record, but I have to "remove filter" to get to the
other forms.

Dave

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message

news:<41******* *************** @per-qv1-newsreader-01.iinet.net.au >...

You need extra quotes:
strWhere = "LastName = """ & Me.TxtSearch & """"