Append data from a form into a different table.

[bosk0]

I have a form that gets its records from a query. What I am trying to do is after looking at the records returned, to append a specific record to a different table. My query gives me the error "Syntax error in INSERT INTO statement" Any help is appreciated.

INSERT INTO [Report Temp] (T01DESM, T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, T01CRTC, T01PROTC
SELECT Me.T01DESM, Me.T01DATE0, Me.T01SSN, Me.T01NAM, Me.T01TCODE, Me.T01AMT, Me.T01EXBSTS, Me.T01EXCSTS, Me.T01EX2STS, Me.T01CHGTC, Me.T01CRTC, Me.T01PROTC

Thank you

 

[MajP]

It cannot be done this way. “Me” can only be used in the form’s vba code to return the form instance. It cannot be used in SQL
Most people do this by creating the sql string in code. The correct format is for an insert requires delimiters around strings and dates.
Something like

INSERT INTO tableName (textField, numberField, dateField) VALUES (‘Text’, 123, #1/1/2010#)

or the word NULL for a null value.

Ex
Dim strSql as string
strSql = “INSERT INTO tableName (textField, numberField, dateField) VALUES (‘” & me.txBxOne & “’,” & me.txtBxtwo & “,#” & format(me.txBxThree,”mm/dd/YY”) & “#)”
currentdb.execute strSql

If you do not want to build this in code an easier way it to make functions in a standard module that return the form values.
Ex.

Public Function getTextField() As Variant
  Dim frm As Access.Form
  Set frm = Forms("frmOne")
  getTextField = frm.textField
End Function
Public Function getNumberField() As Variant
  Dim frm As Access.Form
  Set frm = Forms("frmOne")
  getNumberField = frm.numberField
End Function

Public Function getdateField() As Variant
  Dim frm As Access.Form
  Set frm = Forms("frmOne")
  getdateField = frm.dateField
End Function

Then the sql becomes
INSERT INTO tblTwo ( textField, numberField, dateField )
VALUES (getTextField(), getNumberField(), getDateField());
This is nice because it handles null values without any problem.

You would need functions like
getT01DESM()

 

[bosk0]

MajP, Thank you for your response. I was able to set up the various public functions for the fields on the form. If you would be able to point me in the right direction. I have made a button are attempting to run the sql code from the on click.

I am having trouble with the code. I get compile error, expected: expression.

Here is the code I have.

Private Sub Append_Record_Click()

DoCmd.RunSQL

    strSQL = INSERT INTO [Report Temp] (T01DESM, T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, T01CRTC, T01PROTC
VALUES (getT01DESM(), getT01DATE0(), getT01SSN(), getT01NAM(), getT01TCODE(), getT01AMT(), getT01EXBSTS(), getT01EXCSTS(), getT01EX2STS(), getT01CHGTC(), getT01CRTC(), getT01PROTC());
End Sub

Thanks in advance

 

[Andrzejek]

You may start by:

Private Sub Append_Record_Click()
Dim strSQL As String

strSQL = "INSERT INTO [Report Temp] (T01DESM, " & _ 
" T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, " & _ 
" T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, " & _ 
" T01CRTC, T01PROTC" & _ 
" VALUES ([blue]getT01DESM(), getT01DATE0(), " & _ 
" getT01SSN(), getT01NAM(), getT01TCODE(), " & _ 
" getT01AMT(), getT01EXBSTS(), getT01EXCSTS(), " & _ 
" getT01EX2STS(), getT01CHGTC(), " & _ 
" getT01CRTC(), getT01PROTC())[/blue];"

Debug.Print strSQL

DoCmd.RunSQL strSQL

End Sub

But you need to take a closer look at the BLUE portion of this code.

Have fun.

---- Andy

 

[bosk0]

The Blue section is all of the functions that were set up for the fields on the form.

All are set up like

Public Function getT01DESM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01DESM = frm.T01DESM
End Function

When the button is pressed I get runtime error 3134
syntax error in Insert into statement. If I pick debug it highlights the DoCmd.RunSQL strSql line.

 

[MajP]

Sorry, I was confusing. From your original post I thought you were trying to do this in the query builder. This approach would work if you did it that way. So my idea was to make this a saved query. Lets call it “yourQuery”
Then your code is simply
Docmd.runsql “yourQuery”

If you build the sql string in code it will not work as shown. You could still use the functions, but you would have to modify them to then handle dates and strings as mentioned. Also your sql string would have to be more like this

Dim strSql as string

strSQL = “INSERT INTO [Report Temp] (T01DESM, T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, T01CRTC, T01PROTC VALUES (“
strSql = strSql & getT01DESM() & “, ” & getT01DATE0() & “, “ & getT01SSN() & “, “ & getT01NAM() & “, “ & getT01TCODE() & “, “ getT01AMT() & “, “ & getT01EXBSTS()& ”, “ & getT01EXCSTS() & “, “ getT01EX2STS() “, “ & getT01CHGTC() & “, “ & getT01CRTC() & “, “ & getT01PROTC() & “)”
currentdb.execute strSql

now if your function is returning a text value you need to return the string wrapped in single quotes
getT01DESM = “’” & frm.someControl & “’”
if it is a date you may have to format it like
getTO1DATE0 = “#” & format (frm.somedatecontrol,”MM/DD/YYYY”) & “#”

 

[bosk0]

I changed the strSQL statement and if I understood correctly, the functions needed to changed to

Public Function getT01DESM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01DESM = "" & frm.T01DESM & ""
End Function

when it is run it gives a "runtime error 28 out of stack space" If I pick debug it takes me to the second function. I changed the order of the functions to check if it was a problem with a particular function. It is not, always errors at the second. Any thoughts?

Thanks for all of the help.

 

[MajP]

if you are writing the sql string in code please post what you have.

If you are doing this in code when you resolve the sql string should look like

INSERT INTO tableName (textField, numberField, dateField) VALUES ('Text', 123, #1/1/2010#)

If it is text it gets surrounded by single quotes, and a date is sorrrounded by #.

So this should be (with single quotes)
getT01DESM = "'" & frm.T01DESM & "'"
not
getT01DESM = "" & frm.T01DESM & ""

your version just puts in spaces not single quotes. This will cause the sql to fail.

 

[bosk0]

I really appreciate all of the help.

After adding the single quotes to the functions. It still gives the same error.

This is the code on the form

Public Function getT01DESM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01DESM = "'" & frm.T01DESM & "'"
End Function
Public Function getT01DATE0() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01DATE0() = "#" & Format(frm.T01DATE0, "MM/DD/YYYY") & "#"
End Function
Public Function getT01SSN() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01SSN() = "'" & frm.T01SSN & "'"
End Function
Public Function getT01NAM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01NAM() = "'" & frm.T01NAM & "'"
End Function
Public Function getT01TCODE() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01TCODE() = "'" & frm.T01TCODE & "'"
End Function
Public Function getT01AMT() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01AMT() = "'" & frm.T01AMT & "'"
End Function
Public Function getT01EXBSTS() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01EXBSTS() = "'" & frm.T01EXBSTS & "'"
End Function
Public Function getT01EXCSTS() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01EXCSTS() = "'" & frm.T01EXCSTS & "'"
End Function
Public Function getT01EX2STS() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01EX2STS() = "'" & frm.T01EX2STS & "'"
End Function
Public Function getT01CHGTC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01CHGTC() = "'" & frm.T01CHGTC & "'"
End Function
Public Function getT01CRTC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01CRTC() = "'" & frm.T01CRTC & "'"
End Function
Public Function getT01PROTC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01PROTC() = "'" & frm.T01PROTC & "'"
End Function

Private Sub Append_Record_Click()
Dim strSql As String

strSql = "INSERT INTO [Report Temp] (T01DESM, T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, T01CRTC, T01PROTC VALUES (" & _
strSql = strSql & getT01DESM() & ", " & getT01DATE0() & ", " & getT01SSN() & ", " & getT01NAM() & ", " & getT01TCODE() & ", " & getT01AMT() & ", " & getT01EXBSTS() & ", " & getT01EXCSTS() & ",  " & getT01EX2STS() & ", " & getT01CHGTC() & ", " & getT01CRTC() & ", " & getT01PROTC() & ")"

Debug.Print strSql

CurrentDb.Execute strSql
End Sub

 

[MajP]

The error is here on the majority of functions:

getT01SSN() = "'" & frm.T01SSN & "'"
should read
getT01SSN = "'" & frm.T01SSN & "'"

If you include those () it thinks you are doing a recursive call to the function not setting the value. So the function keeps calling itself and thus you run out of stack space. You made that mistake in all but the first function.

Can you post the what appears in the immediate window? That will show me what your string resolves into. Also can you list the data types of your fields (text, numeric, date)?

If it is erroring out on a specific function then comment out the function like so
Public Function getT01DATE0() As Variant
'Dim frm As Access.Form
'Set frm = Forms("RDAOR")
'getT01DATE0() = "#" & Format(frm.T01DATE0, "MM/DD/YYYY") & "#"
End Function

 

[bosk0]

I have cleaned up the functions. I have also added a remark to each telling the type.

In the Immediate window it just says false

I get 1 of 2 errors when run. I am unsure whether the the proper usage should be:

CurrentDB.Execute strSql or
DoCMD.RunSQL strSql

each gives a different error.

the command CurrentDB.Execute strSql gives Run-time error 3078:

The Microsoft Jet database engine cannot find the input table of query 'False'. Make sure it exists and that its name s spelled correctly.

The command DoCMD.RunSQL strSql gives Run-time error 3129:

Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT, or 'UPDATE',

The following is the code on the form

Public Function getT01DESM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01DESM = "'" & frm.T01DESM & "'"
    'TEXT
End Function
Public Function getT01DATE0() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01DATE0 = "#" & Format(frm.T01DATE0, "MM/DD/YYYY") & "#"
    'DATE
End Function
Public Function getT01SSN() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01SSN = frm.T01SSN
    'NUMBER
End Function
Public Function getT01NAM() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01NAM = "'" & frm.T01NAM & "'"
    'TEXT
End Function
Public Function getT01TCODE() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01TCODE = frm.T01TCODE
    'NUMBER
End Function
Public Function getT01AMT() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01AMT = frm.T01AMT
    'NUMBER
    End Function
Public Function getT01EXBSTS() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01EXBSTS = "'" & frm.T01EXBSTS & "'"
    'TEXT
End Function
Public Function getT01EXCSTS() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01EXCSTS = "'" & frm.T01EXCSTS & "'"
    'TEXT
End Function
Public Function getT01EX2STS() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01EX2STS = "'" & frm.T01EX2STS & "'"
    'TEXT
End Function
Public Function getT01CHGTC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01CHGTC = frm.T01CHGTC
    'NUMBER
End Function
Public Function getT01CRTC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01CRTC = frm.T01CRTC
    'NUMBER
End Function
Public Function getT01PROTC() As Variant
    Dim frm As Access.Form
    Set frm = Forms("RDAOR")
    getT01PROTC = frm.T01PROTC
    'NUMBER
End Function

Private Sub Append_Record_Click()
Dim strSql As String

strSql = "INSERT INTO [Report Temp] (T01DESM, T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, T01CRTC, T01PROTC VALUES (" & _
strSql = strSql & getT01DESM() & ", " & getT01DATE0() & ", " & getT01SSN() & ", " & getT01NAM() & ", " & getT01TCODE() & ", " & getT01AMT() & ", " & getT01EXBSTS() & ", " & getT01EXCSTS() & ",  " & getT01EX2STS() & ", " & getT01CHGTC() & ", " & getT01CRTC() & ", " & getT01PROTC() & ")"

Debug.Print strSql

DoCmd.RunSQL (strSql)
'CurrentDb.Execute strSql
End Sub

 

[bosk0]

I found that there was an & _ at the end of the first strSQL line. When that is removed the error returned changes to:

Run-time error '3134'

Syntax error in INSERT INTO statement.

What now appears in the immediate window is:

INSERT INTO [Report Temp] (T01DESM, T01DATE0, T01SSN, T01NAM, T01TCODE, T01AMT, T01EXBSTS, T01EXCSTS, T01EX2STS, T01CHGTC, T01CRTC, T01PROTC VALUES ('0567147', #06/29/2008#, 000000000, 'MCNEIL MICHAEL S', 664, 0, 'Y', 'N', 'N', 0, 0, 0)

 

[PHV]

, T01PROTC[!])[/!] VALUES ("

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bosk0]

PHV and Majp

Thank you to you both for all of your help. The final piece was the missing )