Names using inverted comas!!!

[Marchello2004]

Hi

I have an interesting one here... I have a dropdown list which displays staff name. The name selected will be then stored in a table by using a submit button that works as follows...

Dim strSQL As String

strSQL = "INSERT INTO Bookings (staff_name,) Values ('" & cmbStaff_name & "')"

CurrentDb.Execute strSQL

This all works beautifully; the problem here is that some of the names use inverted comas, such as (O'Donovan) and the code gets confused, so it will not submit anything to the table.

I'm guessing I need a function for this but I have no idea where to start. Does anyone have any idea of how to solve this? And changing the database content is not really an option...

Thanks!!

 

[HarleyQuinn]

Double the single quotes up:

Replace(cmbStaff_name,"'","''")

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Marchello2004]

Yeh thanks

I actually mamanged to do it. Just in case someone is interested. Here is what I did...

Function iCommas(c)
iCommas = Replace(c, "'", "''")
End Function

Then on the SQL String...

Dim strSQL As String

strSQL = "INSERT INTO Bookings (staff_name,) Values ('" & iCommas(cmbStaff_name) & "')"

CurrentDb.Execute strSQL

 

[HarleyQuinn]

Glad you got it sorted

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.