Problem with find button code

  • Thread starter Thread starter Allen
  • Start date Start date
A

Allen

Hey all, I'm having a problem with this code. It is in Access 2003. It is a
find button to find a record by an input box for the ssn of a contact. I
keep getting a syntax error for the expression. Here is the code.

Thanks
Allen

Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sName As String


sName = InputBox("Enter the SSN of the person to find.", "Search for
name", "")

Set db = CurrentDb
Set rs = Me.RecordsetClone


sSQL = "SELECT Contacts.*, Contacts.SSN FROM Contacts WHERE
(((Contacts.SSN)=sName ));"

rs.FindFirst sSQL 'This is where the debugger stops at
If rs.NoMatch Then
'The name was NOT found
Else
'The name was found
End If

rs.Close
Set rs = Nothing
Set db = Nothing
 
Try this:

sSQL = "SELECT Contacts.*, Contacts.SSN FROM Contacts WHERE
Contacts.SSN='" & sName & "'"

Incidentally, its a very bad idea to include ssns in an access database
unless you are absolutely sure the data is secure.

-Dorian
 
Thanks for the info but I cant tell the quote marks. Is it ' " & sName & " '
" ?

Allen
 
I tried both and still get the same error.
If there is an easier way to find a record on a form using a input box
please let me know.

Allen
 
Hi Allen,

To add some to Dorian's answer, I agree that it is very bad to store SSN's
in .mdb files. If you really need SSN, you should be using SQL Server or
Oracle to store the data.

Why Social Security Numbers should not be stored in an Access database
http://www.access.qbuilt.com/html/security.html#DoNotStoreSSNs


According to the Help file, your sSQL statement is not correct:

criteria: A String used to locate the record. It is like the WHERE clause
in an SQL statement, but without the word WHERE. Try this instead:

Private Sub cmdFindPerson_Click()
On Error GoTo ProcError

Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sName As String

sName = InputBox("Enter the SSN of the person to find.", "Search for name",
"")

Set db = CurrentDb
Set rs = Me.RecordsetClone

sSQL = "Categories.CategoryName= " & Chr(34) & sName & Chr(34)

sSQL = "Contacts.SSN= " & Chr(34) & sName & Chr(34)

rs.FindFirst sSQL
If rs.NoMatch Then
MsgBox "No match was found." 'The name was NOT found
Else
MsgBox "A match was found." 'The name was found
End If

rs.Close
Set rs = Nothing
Set db = Nothing

ExitProc:
' Cleanup
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdFindPerson_Click..."
Resume ExitProc
End Sub


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Try this:

sSQL = "SELECT Contacts.*, Contacts.SSN FROM Contacts WHERE
Contacts.SSN='" & sName & "'"

Incidentally, its a very bad idea to include ssns in an access database
unless you are absolutely sure the data is secure.

-Dorian
__________________________________________

:

Hey all, I'm having a problem with this code. It is in Access 2003. It is a
find button to find a record by an input box for the ssn of a contact. I
keep getting a syntax error for the expression. Here is the code.

Thanks
Allen

Dim db As Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sName As String


sName = InputBox("Enter the SSN of the person to find.", "Search for
name", "")

Set db = CurrentDb
Set rs = Me.RecordsetClone


sSQL = "SELECT Contacts.*, Contacts.SSN FROM Contacts WHERE
(((Contacts.SSN)=sName ));"

rs.FindFirst sSQL 'This is where the debugger stops at
If rs.NoMatch Then
'The name was NOT found
Else
'The name was found
End If

rs.Close
Set rs = Nothing
Set db = Nothing
 
PS. I forgot to delete these lines of code:

rs.Close
Set rs = Nothing
Set db = Nothing

after moving them to the ExitProc: section. I did this so that these objects
will always be closed and destroyed, even if an error is encountered in the
procedure.

Also, as far as finding records, you might want to try this mini tutorial:

Combo box to find a record
http://www.access.qbuilt.com/html/find_a_record.html


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
 
Thanks now its finding the record but not going to it. How do I make it
move to the found record? I will look at the tutorial.

Allen
 
I looked at the tutorial and I have another question for some reason none of
my wizards show up when I add a control. I just upgraded from 2000 to 2003
and now they dont work which is why I have to hard code everything.

Allen
 
Hi Allen,

Do you have the wizards activated on the toolbox. Toggle the toolbox button
that looks like a wand, then try the wizard again.

If the wizards still fail to work, then try the solution I posted here:

http://www.microsoft.com/office/com...nfig&mid=e7dcca41-f3cb-4a0c-abbe-555a00a672e2

I need to leave for work now, so it will be about 10 hours before I can get
back to the newsgroup.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I looked at the tutorial and I have another question for some reason none of
my wizards show up when I add a control. I just upgraded from 2000 to 2003
and now they dont work which is why I have to hard code everything.
 
Back
Top