Memo field "" anomoly

[TryingMyBest]

Hi,

I have several memo fields in my application which all work well until someone uses quotation marks in their text. At this point the operation to update the table fails.

Is there a way round this please? Or is there a way to display a message if the memo field contains a certain character? The user's typically copy and paste text into the memo fields.

An example of my code for updating the table is as follows:

    Dim strSQLDescription As String
    strSQLDescription = "UPDATE TBLNCRRecords SET TBLNCRRecords.Description = """ & [Forms]![frmNonConformities]![txtNCRDescription] & """ WHERE (TBLNCRRecords.NCRNumber) = " & [Forms]![frmNonConformities]![txtNCRNumber] & ";"
    DoCmd.RunSQL strSQLDescription

Jo

I'm using Access 2002 on Windows XP.

 

[ghudson]

I would use the Replace() function and remove the " in the BeforeUpdate event of the memo field in the form. That should eliminate your update problem.

 

[TryingMyBest]

Sorry I'm being dumb. This is what I have in the BeforeUpdate event of the form.

=Replace([txtNCRDescription]," " "," ' ")

I get an error when the after update code runs now: Syntax error (missing operator) in query expression...

Jo

 

[ghudson]

You need to use VBA and put this in the Forms BeforeUpdate event...

txtNCRDescription = Replace([txtNCRDescription], Chr(34), "")
txtNCRDescription = Replace([txtNCRDescription], Chr(39), "")

Chr(34) is the ASCII equivalent to the double quote " and Chr(39) is for the single quote '

 

[WayneRyan]

Jo,

Sometimes if they've gotta maintain the double-quotes in the data:

This doesn't care about single/double quotes.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Select * " & _
                                  "From TBLNCRRecords  " & _
                                  "Where NCRNumber = " & [Forms]![frmNonConformities]![txtNCRNumber])
rst.Edit
rst!Description = [Forms]![frmNonConformities]![txtNCRDescription]
rst.Update

No error trapping, but ...

Wayne

 

[TryingMyBest]

Thanks

I've used Wayne's method as I feel they probably will want to maintain the quotes at times.

Works perfectly....thanks for all your help (both of you)

Jo