AfterUpdate Event Procedure

[MNNovice]

My form is called fItems that is based on a table called Items.

A text box named iTitle is added to fItems. To avoid duplication of a title I added the following codes as an After_Update for iTitle. It's working with one problem. If I have an apostrophe in a title I get an error message:
Run Time Error '3075': Syntax error (missing operator) in query expression '[iTitle] = "Who's Coming to Dinner"

Private Sub iTitle_AfterUpdate()
Dim iTitle As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    iTitle = Me.iTitle.Value
    stLinkCriteria = "[iTitle]=" & "'" & [iTitle] & "'"
    
    'Check Items table for duplicate iTitle
    If DCount("iTitle", "Items", _
              stLinkCriteria) > 0 Then

              
        'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Item Title " _
             & iTitle & " has already been entered." _
             & vbCr & vbCr & "You will now been taken to the record.", _
               vbInformation, "Duplicate Information"
        'Go to record of original Title
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
    End If

    Set rsc = Nothing
End Sub

Can someone please help me correct this coding? Many thanks.

[code green]

You either need to use

Replace(string, search for, replace with

Or double up the offending character

[MNNovice]

Sorry, but I don't understand your suggestions. I am novice at coding. Thanks.

[code green]

Things like commas, quotes, slashes buried within text can cause problems with many programming languages.
So your problem is not unusual particularly from a form.

It is a good idea to write a form input cleaning function to handle this problem.
This can be modified to suit your needs but at he moment you need

cleanString = Replace(dirtyString, character,"")

This will remove character from dirtyString
Or the quote can be escaped by adding another quote

cleanString = Replace(dirtyString, character,"''")

But this is more useful for database insertion

[MNNovice]

You either need to use

Replace(string, search for, replace with

Or double up the offending character

Code Green,
Thanks for your kind information. While I was waiting for your response, I searched the internet and found this solution to my problem:

stLinkCriteria = "[iTitle]=" & "'" & Replace(Me![iTitle],"'","''") & "'"

I added this code to replace:

stLinkCriteria = "[iTitle]=" & "'" & [iTitle] & "'"

This took care of my problem. My question to you:

1. Does this new code have any limitations?
2. How does your code:

cleanString = Replace(dirtyString, character,"")

differ from the one I just used?
3. If your code is the best way to take care of any possible dirty string issues, where am I supposed to add this code? Please be specific and tell me where to insert this new code between which lines?

Many thanks. I wait for your response.


My question to you

[code green]

The two pieces of code clean the input in the same way using Replace().
It is just that the example you found does some additional concatenation.

You need to insert the code just after you read the data.
But I seriously recommend writing a function and calling that, rather then using inline code.