473,324 Members | 2,254 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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_Click

Dim strWhere As String
If Not IsNull(Me.TxtSearch) Then
strWhere = "LastName = " & Me.TxtSearch
End If
DoCmd.OpenForm "Contacts", , , strWhere
rem Err_Button197_Click:
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
Nov 13 '05 #1
3 7622
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*****@hotmail.com> wrote in message
news:d2**************************@posting.google.c om...
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_Click

Dim strWhere As String
If Not IsNull(Me.TxtSearch) Then
strWhere = "LastName = " & Me.TxtSearch
End If
DoCmd.OpenForm "Contacts", , , strWhere
rem Err_Button197_Click:
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

Nov 13 '05 #2
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*********@SeeSig.Invalid> wrote in message news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
You need extra quotes:
strWhere = "LastName = """ & Me.TxtSearch & """"

Nov 13 '05 #3
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.RecordsetClone
.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*****@hotmail.com> wrote in message
news:d2**************************@posting.google.c om...
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*********@SeeSig.Invalid> wrote in message

news:<41**********************@per-qv1-newsreader-01.iinet.net.au>...
You need extra quotes:
strWhere = "LastName = """ & Me.TxtSearch & """"

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Les Juby | last post by:
A year or two back I needed a search script to scan thru HTML files on a client site. Usual sorta thing. A quick search turned up a neat script that provided great search results. It was fast,...
1
by: N. Graves | last post by:
Hi, I want to have a Search Dialog box that has several text box and fields to build a search and display the results in a form. I can do everything that I need to if I us a report but I would...
4
by: Jan | last post by:
I am having problems trying to print a report based on a form. This is a search form only, no data input. There is a query that the form looks at, but then there are numerous comboxes that you...
9
by: Christopher Koh | last post by:
I will make a form which will search the database (just like google interface) that will look/match for the exact name in the records of a given fieldname. Any suggestions on how to make the code?
8
by: Steph | last post by:
Hi. I'm very new to MS Access and have been presented with an Access database of contacts by my employer. I am trying to redesign the main form of the database so that a button entitled...
1
by: ratnakarp | last post by:
Hi, I have a search text box. The user enters the value in the text box and click on enter button. In code behind on button click i'm writing the code to get the values from the database and...
9
by: AMBLY | last post by:
Hello ! Hope someone might be able to help me with this one. I run Access2000 on XP. I have a form : frmONE- which contains a txt field: ctrCTN from my table/database. The values in ctrCTN are...
1
Merlin1857
by: Merlin1857 | last post by:
How to search multiple fields using ASP A major issue for me when I first started writing in VB Script was constructing the ability to search a table using multiple field input from a form and...
5
by: Fran Jakers | last post by:
Hello all, I'm new to all this and I could really use some help. I've searched the web but cannot find an answer. I have an HTML form with 3 radio buttons and a search field that calls a...
2
by: Mark | last post by:
Hi All, I am creating a music database for a friend and have run into a problem. Within the ALBUM table, I wanted to store the ARTIST_ID rather than the ARTIST_NAME. To do this, I intended to have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.