Full Version: Memo Field
UtterAccess Forums > Microsoft® Access > Access Forms
I have a memo field that my users use to update their notes. Once the users tab out of the record a message comes up and tells the users that your going to update one row. The users will click yes to update and the notes appears and is saved to the form. The problem that I have is that one user was not able to update their memo field after they tab out of the record. Can someone tell me why this occurs. The code works on other records, but for some unknown reason this record won't update.
Near is the code that I use.
Private Sub NewComment1_AfterUpdate()
DoCmd.RunSQL "UPDATE tblALL_Data SET [Comment] = '" & NewComment1 & "' WHERE LineID=" & MainLine.Value
End Sub
Does the Comment have any single or double quotes in it? These can easily malform the SQL statement you're building behing the scenes.
I did a test on another record using single and double quotes and sure enough this hose up the record.
Is their a way to deal with this? (Besides telling my tech not to use single and double quotes)
Yes. Before you pass the value of the text box to the SQL statement, double up the quotes.
It's a deal.
It''s a deal.
(two single quotes)
Do this with a simple REPLACE function and the SQL will be inserted without a problem.
Just a note here that DoCmd.RunSQL is not the recommended way to run SQL from code.
urrentDB.Execute (DAO) or CurrentProject.Execute (ADO) is preferred; it's faster and doesn't have the issues with having to turn warnings off and back on (which can lead to other problems).
Another way you can do this is by checking to see if the user presses the single or double quotes while they are typing and have a pop up box telling them they can't do this.
On the forms OnLoad event, have the following code:
Me.KeyPreview = True

Then on the Memo field in the On Key Press have the following code:
If KeyAscii = 39 Or KeyAscii = 34 Or KeyAscii = 124 Then
    MsgBox "You can not use that key!  Please remove them from your comments!", vbCritical, "INVALID KEYS"
End If

When the user is typing in the memo field and hits, a single quote, double quote, or a pipe delimited character, they will get this message.
So what your saying is to create a function (call it Double Quote) to replace the Single quote to double quote. Is this correct?
When on the memo field, I would call the function in the event procedure. Would this be in AfterUpdate event?
If this is incorrect, please provide a example.
Thanks again
CurrentDB.Execute "UPDATE tblALL_Data SET [Comment] = '" & Replace(NewComment1,"'","''") & "' WHERE LineID=" & MainLine.Value, dbFailOnError
Thanks Peter
This works alot faster and for some reason it eliminates the popup box telling the user that your about to update 1 row. this saves a step for the user.
Can you explain why please?
I'm trying to understand why this works faster than my method.
I want to thank everyone for contributing to my question. It folks like you that keeps me comming back, to learn more about Ms Access and VBA etc.
As I mentioned in a post above:
It sounds like you weren't turning warnings off and on around your code, which is why your users were getting the popup box (that was considered a "warning").
I did some more testing and was able to add notes okay even with the single quote, but now I can't delete the note i just entered. Is their additional code that I need to add/
I use your modification. CurrentDB.Execute "UPDATE tblALL_Data SET [Comment] = '" & Replace(NewComment1,"'","''") & "' WHERE LineID=" & MainLine.Value, dbFailOnError
I tried this again in my test db and get a error message of 94.
nvalid use of null.
Can you assist me on this one?
Let's see if I can explain the quotes issue first.
Let's say your comment (in the control NewComment1) was It's taken care of and MainLine was 5.
When you built your SQL string, this is what it looked like after all the string concatenation was done:
UPDATE tblALL_Data SET [Comment] = 'It's taken care of' WHERE LineID=5
The problem here is the only thing that makes sense to the SQL parser is the bolded part. The apostrophe in It's closes the string assignment, and the parser doesn't understand the rest. So, to fix it, we have to replace the single apostrophe with a double apostrophe, which the parser will interpret as "this means one apostrophe IN the string, not the end of a string". So, with the REPLACE function in there, we would get:
UPDATE tblALL_Data SET [Comment] = 'It''s taken care of' WHERE LineID=5
which will have the desired result.
Now, as to the next problem; handling the situation where NewComment1 is NULL (nothing is entered). The field in your table is likely set up (properly, I would add) to not allow empty strings, so we can't just set the comment to an empty string (''). Additionally, the REPLACE function expects a string of some sort to be passed to it, and will balk on a NULL. So, we need to test for and handle NULL/empty string as a separate condition:
If Len(Me.NewComment1 & "") = 0 Then
  CurrentDB.Execute "UPDATE tblALL_Data SET [Comment] = Null WHERE LineID=" & Me.MainLine, dbFailOnError
  CurrentDB.Execute "UPDATE tblALL_Data SET [Comment] = '" & Replace(Me.NewComment1,"'","''") & "' WHERE LineID=" & Me.MainLine, dbFailOnError
End If
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.