Syntax error

[smikkelsen]

Hi, I have a simple problem, but not having much luck fixing it.



I have a command button that writes data to a table. shown here:

strSQL = "Insert Into Main_Tracking_Notes(comment,user,Auto_Date,Main_Tracking_ID) Values(" & "'" & strConString & "'," & "'" & strConString2 & "'," & "'" & strConString3 & "'," & "'" & strConString4 & "'); "

strConString is a variable storing text in a comment box from the user. Basically, I am just getting syntax errors when certain characters like ' . are used in the comment box. For example, if I were to type in " tom's food is getting cold" it would throw a syntax error because of the ' in "tom's" How do i ignore, or treat those as literal text rather than characters that is throwing the syntax off?

Are there any other characters I should do the same to?

Thanks!

[maximus]

To bypass this problem What I do is:

strConName = """" & Me.Text0 & """"
strSQl = "INSERT INTO Table1(D_Name) Values(" & strConName & ");"

Now you will be able to enter names like Tom's, Harry's. I checked your sytanx. If your entering date that has to be enclosed in #.

strSQL = "Insert Into Main_Tracking_Notes(comment,user,Auto_Date,Main_Tr acking_ID) Values(" & "'" & strConString & "'," & "'" & strConString2 & "'," & "#" & strConString3 & "#," & "'" & strConString4 & "');"

[smikkelsen]

awesome thanks!

[smikkelsen]

Sorry, I am getting a new syntax error now. Here is the whole chunk of code I am using for this section:

If IsNull(Me.Text0) Then
MsgBox "Enter Note First!", vbInformation, "No Text"
Else
If [pmiDBcheck] = -1 Then
strConString = "<PMI Note>  " & Me.Text0
[pmiDBcheck] = 0
Else
'strConString = Me.Text0
strConString = """" & Me.Text0 & """"
End If
strConString2 = fOSUserName()
strConString3 = Now()
strConString4 = Me.ID
'strSQL = "INSERT INTO Table1(D_Name) Values(" & strConName & ");"
strSQL = "Insert Into Main_Tracking_Notes(comment,user,Auto_Date,Main_Tracking_ID) Values(" & strConString & "," & "'" & strConString2 & "'," & "'" & strConString3 & "'," & "'" & strConString4 & "'); "

It is only happening when pmiDBcheck =-1 (the if). It is having a hard time when I add literal text, and the me.text0

How can I get around this? I guess I don't understand how the 4 quotes in a row works.

Thanks.

[maximus]

I have tried to replicate the problem that you are facing. When a Check box is checked a Auto text is added to the value of a text box and a string is constructed which is entered into a table using sql. Below is the code


Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim strCon As String
Dim strSQL As String
Dim strID As String
If Me.Check10 = True Then
strID = "<PCB>" & Text8
strID = """" & strID & """"
Else
strID = """" & Me.Text8 & """"
End If


strCon = Now()

strSQL = "INSERT INTO Table1(DA,iD) Values(#" & strCon & "#," & strID & ");"
CurrentDb.Execute strSQL
MsgBox strSQL

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

End Sub

observe how I have used the four double quotation to compensate for strings which include single quote in it like Tom's Jack's

Suppose the The Text Box value is = Jack's. This will be the result

When Check box is Checked
strID="<PCB>Jack's"

When Checked Box is not Checked
strID="Jack's"

Note: This code is capable of handling strings without single quote in it. i.e. if the name is Jack you will still be able to run the code.

I am attaching a small sample mdb for your reference.

Now if this solves your problem please mark the thread solved.

How to use the sample:

The mdb will open with a startup form. type a text into the text box. click the check box to add auto text <PCB>.

Click command button enter data to enter data into Table1

[maximus]

is your problem solved if yes mark this thread solved. Else let me know any new problems that you might be facing.

[smikkelsen]

Yes, worked perfectly! Thanks a million!