Printable View
Private Sub Find_Click()
Dim var As String
var = varHomePhone
With Me.Data1.Recordset
.FindFirst "HomePhone='" & var & "'"
If .NoMatch Then
MsgBox " You Must first add this customer"
AddCust.Show vbModal
End If
End With
End Sub
Can any one give me the code to do this in SQL?
Thanks
Paul
[not tested]
Dim DB as database
dim rs as recordset
Set db = workspaces(0).Opendatabase("dbname")
set rs = db.openrecordset ("select * from tablename where HomePhone='" & varHomePhone & "'", dbopensnapshot)
if rs.eof = true then
MsgBox " You Must first add this customer"
AddCust.Show vbModal
end if
Get in the habit of using chr(34) in place of single or double quote characters around the criteria.
"SELECT * FROM tbl WHERE tbl.Fld = " & chr(34) & x & chr(34)
Will allow you to save and then search for strings that have embedded quotes (users want to hold names like "O'Brien"? now you can) and make building string variables easier.
-robert
Better yet, assign a constant to contain the value:
QUOTE = chr(34)
"SELECT * FROM tbl WHERE tbl.Fld = " & QUOTE & x & QUOTE
Cost you next to nothing and is infintely more readable.
Even easier is to just use "double double-quotes" (wow!)
Eg. mystring = "Here is a quote ("") for you."
? mystring would show:
Here is a quote(") for you.
So: you can use it in SQL like this
strSQL = "SELECT * FROM myTable WHERE mystring = """ & strCheckString & """ AND... etc
Notice the THREE quotes after mystring =... - the third ends the first bit of the string - looks a bit strange but you soon get used to it and don't have to remember to declare a quote character or the ascii no of the quote character.
Hope it helps!
------------------
Chris Keeble
www.giglist.com
mailto:[email protected]
I've been known to do it all 3 ways. Which one I use depends on, 1) how much time I have, 2) the last place I applied SQL code (I work on MVS DB/2, SQL Server 6.5 [just starting 7.0], and Access and 3) how permanent the code is going to be, for me.