Dont allow quote marks in fields

[treva26]

Hi,

How can I prevent people from including quote marks when entering data?

It messes things up when I try to use that field later in an SQL statement from VBA.


For example, I have a field called [Comments1] and someone enters:

This is a very boring "comment".

Then later I run this from VBA using an INSERT or UPDATE SQL string:

DoCmd.RunSQL (SQL)

It doesn't work.

 

[The_Doc_Man]

If the data gets entered on a form, it will be in a text box. You can scan the text box for allowed or disallowed characters by looping through the characters in the box one at a time in an After_Update or Lost_Focus event.

The scanning part would be a loop that uses the MID$ function on the text box's value string for a character length of 1 and for each pass through the loop, just check whether that character is one you didn't want to see. If it is not, still in the routine, set the focus back on the control with the no-no character (using .SetFocus) and pop up a message box that says "you can't use quote marks on input. try again".

In the help files, you want AfterUpdate event (for controls) or LostFocus event. Look up Mid$ and the loop structure. For the pop-up message box use the MsgBox function or subroutine call.

 

[treva26]

Cool thanks.

I figured I would just replace the text and not bother telling the user in this case, and wrote this function to do it.

Public Function myReplace(ByVal string1 As String, replace1 As String, with1 As String) As String
Dim c1 As String, x As Integer

For x = 1 To Len(string1)
c1 = Mid(string1, x, 1)
If c1 = replace1 Then c1 = with1
myReplace = myReplace & c1
Next x

End Function

I saw somewhere that you can use the normal replace function but I couldnt get it to work with double quotes in a forms field.
Can anyone figure out why?!

 Me.FieldName = Replace(Me.FieldName, Chr(34), "`")
 Me.FieldName = Replace(Me.FieldName, """", "`")

Neither of these work.

 

[treva26]

Actually they both work fine, there was something else going on with my form that was screwing it up.