Syntax Error...missing operator

[jgelpi16]

I have a piece of code that stores a selection a user makes in a sub form. Code is below for reference. Whenever a user selects option 9 It throws the error message

"Run-time error '3075':
Syntax error (missing operator) in query expression "data stored in varReason is displayed here".

What am I doing wrong?!?!??! If they select any other reason it works just fine. Thanks for your help in advance.

Dim varFormReason
Dim varReason As String
Dim varDenSelect
Dim varOfferID
Dim varDocDesc
Dim varDenFrameValue
Dim mySQL
Dim varOfferIDCheck
Dim db   As DAO.Database
Dim rst As DAO.Recordset
varDenFrameValue = Abs(Me.frameDenialOptions.Value)
    If varDenFrameValue = 0 Then
        MsgBox "Must select reason before saving.", vbOKOnly
        Exit Sub
    End If
    If varDenFrameValue = 1 Then
        varReason = Form_SfrmDenialReason.lblReason_1.Caption
    End If
    If varDenFrameValue = 2 Then
        varReason = Form_SfrmDenialReason.lblReason_2.Caption
    End If
    If varDenFrameValue = 3 Then
        varReason = Form_SfrmDenialReason.lblReason_3.Caption
    End If
    If varDenFrameValue = 4 Then
        varReason = Form_SfrmDenialReason.lblReason_4.Caption
    End If
    If varDenFrameValue = 5 Then
        varReason = Form_SfrmDenialReason.lblReason_5.Caption
    End If
    If varDenFrameValue = 6 Then
        varReason = Form_SfrmDenialReason.lblReason_6.Caption
    End If
    If varDenFrameValue = 7 Then
        varReason = Form_SfrmDenialReason.lblReason_7.Caption
    End If
    If varDenFrameValue = 8 Then
        varReason = Form_SfrmDenialReason.lblReason_8.Caption
    End If
    If varDenFrameValue = 9 Then
'        Forms![frmNON-CORPOfferInformation].Requery
        varReason = Forms![frmNON-CORPOfferInformation].DenReason.Value
        RunCommand acCmdSaveRecord
    End If
    
    DoCmd.SetWarnings False
    Set db = Access.Application.CurrentDb
    Set rst = db.OpenRecordset("DenialReason")
    varDenSelect = frameDenialOptions.Value
    varOfferID = Forms![frmNON-CORPOfferInformation].OfferID.Value
    
    varDocDesc = "QryDelDenialReason"
    DoCmd.OpenQuery varDocDesc, acViewNormal
    mySQL = "INSERT INTO DenialReason (OfferID, DenialReason, DenSelect) VALUES ('" & varOfferID & "','" & varReason & "', '" & varDenSelect & "')"
    CurrentDb.Execute mySQL, dbFailOnError
'    MsgBox "Denial Reason Saved.", vbOKOnly
    DoCmd.SetWarnings True

[HiTechCoach]

Try removing the .Value part.

[pbaldy]

What does varReason contain at that point (specifically)?

[ssanfu]

Remove the line "DoCmd.SetWarnings False". It's not needed.

Is the form "Forms![frmNON-CORPOfferInformation]" open??

You need to (should) comment your code.......

Why do you have this line: DoCmd.OpenQuery varDocDesc, acViewNormal ?

Put a breakpoint at the beginning of the code and single step thru the code. What line does the error occur???



Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

[jgelpi16]

The error always occurs on the:

CurrentDb.Execute mySQL, dbFailOnError

after

    mySQL = "INSERT INTO DenialReason (OfferID, DenialReason, DenSelect) VALUES ('" & varOfferID & "','" & varReason & "', '" & varDenSelect & "')"

I just figured out the issue....what had happened is this.

The text in the Forms![frmNON-CORPOfferInformation].DenReason.Value had a " ' " in it (Customer's). How can I get around this? The user needs to be able to use the " ' " in the text box.

[weekend00]

if the use can use ', then you can not use it. use "" instead.

[jgelpi16]

Unfortunately that did not work. When I tried to use Customer"'"s it gave me the same syntax error message. I guess I'll just have to live without it...

[weekend00]

I mean you don't use ' in you code. do not need to change the user data/value

[jgelpi16]

Forgive me but I'm not quite sure I follow. Are you referring to this piece of code?:

mySQL = "INSERT INTO DenialReason (OfferID, DenialReason, DenSelect) VALUES ('" & varOfferID & "','" & varReason & "', '" & varDenSelect & "')"

What would I use instead of the ' in this part of the code?

[weekend00]

mySQL = "INSERT INTO DenialReason (OfferID, DenialReason, DenSelect)
VALUES (""" & varOfferID & """,""" & varReason & """, """ & varDenSelect & """)"

[jgelpi16]

That worked. Thank you. Can you explain the difference in the two pieces? ( '" & variable & "' AND """ & variable & """).

[weekend00]

if you use ', and there is ' in the user data, Access may confuse and say syntax error.

[pbaldy]

You might find this useful in explaining that problem as well as others:

http://www.baldyweb.com/BuildSQL.htm

[jgelpi16]

Thank you. Website is very helpful.

[pbaldy]

Happy to help.