SQL statement

  • Thread starter Thread starter speederpro
  • Start date Start date
S

speederpro

I have a subform on a main form.

The purpose of the subform is to search for existing
records.

The main form has three textboxes: FName, LName, MName

The onchange procedure executes sqlstatement which updates
the subform.

For example:
when I type in "J" in FName, the subform will updates
itself to show everyone's FName that starts with "J"

Because I am using sql statement, when someone's name has
apostrophe, the sql statement crashes...Does anyone has
solution for it?
 
I didn't know it is necessary. Sorry. Here's the code

Private Sub UpdateSubform()
Dim strSql as string

strSql = "Select * from Contact where FName like "
strSql = strSql & "'" & strFName & "*'"
strSql = strSql & " and LName like '" & strLName & "*'"

Form_Contact_Subform.RecordSource = strSql
Form_Contact_Subform.Requery
end sub

I have tried using Chr(39) to replace apostrophe but
doesn't work

strFName and strLName are form level variables and gets
updated in the FName_Change() procedure.

Private Sub FName_Change()
strFName = FName.text
UpdateSubform
end sub
 
or another way would be to do this in your where clause --
----- '"& me!textbox.text & "'"
 
I didn't know it is necessary. Sorry. Here's the code

Private Sub UpdateSubform()
Dim strSql as string

strSql = "Select * from Contact where FName like "
strSql = strSql & "'" & strFName & "*'"
strSql = strSql & " and LName like '" & strLName & "*'"

Form_Contact_Subform.RecordSource = strSql
Form_Contact_Subform.Requery
end sub

I have tried using Chr(39) to replace apostrophe but
doesn't work
Click to expand...

Chr(39) IS an apostrophe! You're replacing it with itself.

Use " - Chr(34) - to delimit the query criterion. Try:

strSql = "Select * from Contact where FName like "
strSql = strSql & Chr(34) & strFName & "*" & Chr(34)
strSql = strSql & " and LName like " & Chr(34) & strLName & "*" &
Chr(34)
 
Back
Top