×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Getting a syntax error in INSERT INTO statement

Getting a syntax error in INSERT INTO statement

Getting a syntax error in INSERT INTO statement

(OP)
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

CODE

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

CODE

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.

CODE

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

RE: Getting a syntax error in INSERT INTO statement

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

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

CODE

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:

CODE

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:

CODE

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


---- Andy

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

RE: Getting a syntax error in INSERT INTO statement

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

Alan

RE: Getting a syntax error in INSERT INTO statement

I am glad it worked. smile

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




---- Andy

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

RE: Getting a syntax error in INSERT INTO statement

(OP)
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

RE: Getting a syntax error in INSERT INTO statement

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

---- Andy

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close