Allowing user enter a SINGLE QUOTE

[LucyL]

Hi,
I have a situation whereby the user enters in details relating to a customer. I am having difficulty with single quotes however. The code is as follows, where name includes both first and surname. However I am getting an error here *..its looking for an object...any ideas?
thanx

Dim adoconnection As ADODB.connection
Set adoconnection = New ADODB.connection
adoconnection.Open ("Provider=Microsoft.jet.oledb.4.0;" & "Data Source = h:\PropertyManagementSys\NewDatabase.mdb&quot
Set rs = New ADODB.Recordset


*name = Replace(txtsurname.text, "'", "''&quot

rs.Open "select * from Renter", adoconnection, adOpenDynamic, adLockOptimistic

With rs
.MoveFirst
.Find "Name like '" & txtName & "'"
If .EOF Then
.AddNew
!Name = UCase(txtName.Text)
!Address = UCase(cboProperty.Text)
!property_no = UCase(txtPNo.Text)
!Tel_No = UCase(txtNumber.Text)
!Mobile = UCase(txtMobile.Text)
!Lease_Length = UCase(txtLength.Text)
!Letting_Start = UCase(DTPicker1.Value)
!Letting_Finish = UCase(txtFinishDate.Text)
.Update
.Close
Set rs = Nothing
txtName.Text = ""
MsgBox "Information Saved Successfully"
Else
MsgBox "The name " & txtName & " already exists in the database. Please enter an initial or some or distinguishing feature. eg John M Smith"
End If
End With

Unload Me
Renter.SSTab1.Tab = 0
Renter.Show
End Sub

 

[jlbsloan]

Did you try declaring name as a string?

Dim strName as String

 

[bobisto]

You can't insert single quote in string expression and send it to sqlserver.
If you decide to change it to double quote then
!Name = UCase(Replace(txtName.Text, chr(39), chr(34)))
There are another solution. You can replace ' with some other special caracter and do reverse replacement when fething it from sql.

Boban.
Don't kill a pig for one kilogram meat.

 

[strongm]

Do you actually have a control on your form called txtsurname as, if not, you will get Error 424 Object Required? Check for typos.