Getting a syntax error in INSERT INTO statement 1

[bosk0]

I have a form that gets its information from a query. There are 4 fields that are unbound that will be filled in by the user. When a button is pressed it is suppose to take all the information shown on the form from the original query and the filled in information and insert into a table named temp. When it is run I get an error message that there is a syntax error in the INSERT INTO statement.

This was done with functions

Public Function getT01DESM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getT01DESM = "'" & frm.T01DESM & "'"
    'TEXT
End Function
Public Function getCOMPANY() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getCOMPANY = "'" & frm.COMPANY & "'"
    'TEXT
End Function
Public Function getADDRESS1() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getADDRESS1 = "'" & frm.ADDRESS1 & "'"
    'TEXT
End Function
Public Function getCSZ() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getCSZ = "'" & frm.CSZ & "'"
    'TEXT
End Function

Public Function getT01USECDC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getT01USECDC = "'" & frm.T01USECDC & "'"
    'TEXT
End Function

Public Function getNAME1() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getNAME1 = "'" & frm.NAME1 & "'"
    'TEXT
End Function

Public Function getTITLE() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getTITLE = "'" & frm.TITLE & "'"
    'TEXT
End Function

Public Function getVC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getVC = "'" & frm.VC & "'"
    'TEXT
End Function

Public Function getPercent() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getPercent = "'" & frm.Percent & "'"
    'TEXT
End Function

Public Function getSavings() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getSavings = "'" & frm.Savings & "'"
    'TEXT
End Function

Public Function getPayments() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getPayments = "'" & frm.Payments & "'"
    'TEXT
End Function

Public Function getSTATE() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getSTATE = "'" & frm.STATE & "'"
    'TEXT
End Function

Public Function getState_1() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getState_1 = "'" & frm.State_1 & "'"
    'TEXT
End Function

Public Function getState_2() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getState_2 = "'" & frm.State_2 & "'"
    'TEXT
End Function

Public Function getState_3() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getState_3 = "'" & frm.State_3 & "'"
    'TEXT
End Function

Public Function getState_4() As Variant
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getState_4 = "'" & frm.State_4 & "'"
    'TEXT
End Function

The insert is in the following sub

Private Sub Command35_Click()
On Error GoTo Err_Command35_Click

    Dim StrSql As String

    StrSql = "INSERT INTO [Temp] (T01DESM, COMPANY, ADDRESS1, CSZ, T01USECDC, NAME1, TITLE, VC, Percent, Savings, Payments, STATE, State_1, State_2, State_3, State_4) VALUES ("
    StrSql = StrSql & getT01DESM() & ", " & getCOMPANY() & ", " & getADDRESS1() & ", " & getCSZ() & ", " & getT01USECDC() & ", " & getNAME1() & ", " & getTITLE() & ", " & getVC() & ", " & getPercent() & ", " & getSavings() & ", " & getPayments() & ",  " & getSTATE() & ", " & getState_1() & ", " & getState_2() & ", " & getState_3() & ", " & getState_4() & ")"

    Debug.Print StrSql

    DoCmd.RunSQL (StrSql)
    'CurrentDb.Execute strSql
    
    'Dim stDocName As String

    'stDocName = "VC_Report"
    'DoCmd.OpenReport stDocName, acNormal

    'stDocName = "Clean Table"
    'DoCmd.OpenQuery stDocName, acViewNormal
        
Exit_Command35_Click:
    Exit Sub

Err_Command35_Click:
    MsgBox Err.Description
    Resume Exit_Command35_Click
    
End Sub

When I look in the immediate window the informatin is correct, however it does not put anything in the temp table.

INSERT INTO [Temp] (T01DESM, COMPANY, ADDRESS1, CSZ, T01USECDC, NAME1, TITLE, VC, Percent, Savings, Payments, STATE, State_1, State_2, State_3, State_4) VALUES ('222074593000', 'Lincoln Technical Institute, Inc.', '14 Sylvan Way, Suite A', 'Parsippany, NJ  7005', 'CS', 'Mr. Stephen Ace', 'Senior Vp Of Human Resources', '5', '10', '15', '20',  'NJ', 'New Jersey Employment Security Agency', 'State of New Jersey - Department of Labor and Workforce Development', 'Division of Employer Accounts', 'P.O. Box 913, Trenton, NJ 08625-0913')

Any help will be greatly appreciate.

Thank you


Alan

 

[Andrzejek]

[tt]Percent[/tt] is a reserved word in Access and if you want to use it, you need to do: [tt][Percent][/tt]
BTW - all your [tt]Public Functions[/tt] could be replaced with just one simple Function...

Since you always return a String, just pass the name of your control to your Fucntion:

Public Function getValFromField(ByRef strMyField As String) As String
    Dim frm As Access.Form
    Set frm = Forms("Input_Form")
    getValFromField = "'" & frm.Controls(strMyField) & "'"
End Function

and since you ALWAYS go after the same Form, you also may simply do:

Public Function getValFromField(ByRef strMyField As String) As String
    getValFromField = "'" & Input_Form.Controls(strMyField) & "'"
End Function

and to use any of these Functions, just do:

getValFromField("T01DESM") & ", " & getValFromField("COMPANY") & ", " & ...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[bosk0]

Great Post, That was it. Thank you. Your help is greatly appreciated. Here's a well deserved star.


Alan

 

[Andrzejek]

I am glad it worked.

Just curious...
You've entered into NAME1 field a name: [tt]Mr. Stephen Ace[/tt]
What will happen if you have [blue][tt]Mrs. Susan O'Brian[/tt][/blue] instead?
Or any other text with a single quote in any other field




---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[bosk0]

For curiosity, I set it up to use Ms. O'Brian in the NAME1 field. It did not care. It printed out the page with her name on it.



Alan

 

[Andrzejek]

Great!
I asked because I have problems with single quotes.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson