Insert Into Statement

[Artemis Hothmire]

Good afternoon,

I am running into some problems. I am writing the below code for one of my forms:

Private Sub cmdCatalogDeficitValues_Click()
MsgBox "Appending deficit values to history table"
Dim strquery As String
strquery = "INSERT INTO [tblAnnualMidYearBudgetReviewHistory] " & _
"([OddCogTotDefVal], [EvenCogTotDefVal], [GPETETotalDefVal], [IntOddTotDefVal], [IntEvenTotalDefVal], [RepOddTotDefVal], [RepEvenTotDefVal])" & _
"VALUES(" & _
"" & Me.txtOddCogTotalDefVal.Value & "," & _
"" & Me.txtEvenCodTotaDefVal.Value & "," & _
"" & Me.txtRepairCogTotalDefVal.Value & "," & _
"" & Me.txtInitialOddCogDefVal.Value & "," & _
"" & Me.txtInitialIssueEvenCogDef.Value & "," & _
"" & Me.txtReplacementIssueOddCogDef.Value & "," & _
"" & Me.txtReplacementIssueEvenCogDef.Value & "," & _
")"
DoCmd.RunSQL strquery
MsgBox "Deficit values have been added"
End Sub

The problem is I keep getting an error, error number 3134 (Syntax Error) on the docmd.runsql portion. I have double checked everything and there are no mispellings, so thats not it. And the punctuation is the same as another VBA code on another form, only the fields and tables have changed. I cant figure out whats wrong. Please help.

 

[pbaldy]

This should help:

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

Offhand, stray comma.

 

[Artemis Hothmire]

Where do you think the stray comma is? And how do I use debug.print? I am still rather new to Access.

 

[jdraw]

Here is another reference for debugging vba. It shows you how to use the debug.print.
http://www.cpearson.com/excel/debug.htm
Good luck.

 

[pbaldy]

Were the instructions in my link not clear enough? I can change it if so. I know the stray comma is the one not followed by a field name or value.

 

[Artemis Hothmire]

Its not that it wasn't clear, I am new to access and dont really understand that much yet. Still on basic queries and such. I put in debug.print before the docmd.runsql portion, but i didnt notice any thing different. Also I took away the last comma at the end and it still gives my a snytax error.

 

[pbaldy]

The link specifies:

Debug.Print YourVariableName

which in your case would be

Debug.Print strquery

Logically it would go between the line setting the variable and the line executing it. The link also shows where the output will be.

 

[Artemis Hothmire]

I have learned how to use it, and have. But i still dont know what im looking for, or what is wrong. Im

 

[pbaldy]

What is the code now, the result from the Immediate window, and the error now? You realize that text and date fields require delimiters? You're treating all the fields as numeric.

 

[michaeljryan78]

I may want to chime in here. PBaldy, is is good to teach a man to fish... so lets give 'em some bait...
if the values that are in the text boxes are text or date, they must be surrounded by qoutes (for text) or hash tags (for dates). Careful for Nulls too.

Hit ALT+F11 to go to the editor and place a breakpoint on your code at Private Sub cmdCatalogDeficitValues_Click() by left clicking in the "grey" margin on the left. you will see a maroon circle there. Click the button on the firm to execute the code and press F8 to step through. when you hit the debug.print line hit F8 again and look at the immediate window, the SQL statement will be there...

Function quote(str As Variant) As String
    If IsNull(str) Then
        quote = "null"
    Else
        quote = """" & Replace(Trim(str), """", "'") & """"
    End If
End Function
 
Private Sub cmdCatalogDeficitValues_Click()
MsgBox "Appending deficit values to history table"
Dim strquery As String
 
strquery = "INSERT INTO [tblAnnualMidYearBudgetReviewHistory] " & _
"([OddCogTotDefVal], [EvenCogTotDefVal], [GPETETotalDefVal], [IntOddTotDefVal], [IntEvenTotalDefVal], [RepOddTotDefVal], [RepEvenTotDefVal])" & _
"VALUES(" & _
"" & quote(Me.txtOddCogTotalDefVal.Value) & "," & _
"" & qoute(Me.txtEvenCodTotaDefVal.Value) & "," & _
"" & Quote(Me.txtRepairCogTotalDefVal.Value) & "," & _
"" & quote(Me.txtInitialOddCogDefVal.Value) & "," & _
"" & quote(Me.txtInitialIssueEvenCogDef.Value) & "," & _
"" & quote(Me.txtReplacementIssueOddCogDef.Value) & "," & _
"" & quote(Me.txtReplacementIssueEvenCogDef.Value) & ")"
debug.print strQuery
 
'the abouve statement will show the SQL string in the immediate window in the VBA editor.
 
Currentdb.execute(strquery)
MsgBox "Deficit values have been added"
End Sub

try that out and see if the problem is still there.

 

[Solo712]

Good afternoon,

I am running into some problems. I am writing the below code for one of my forms:

Private Sub cmdCatalogDeficitValues_Click()
MsgBox "Appending deficit values to history table"
Dim strquery As String
strquery = "INSERT INTO [tblAnnualMidYearBudgetReviewHistory] " & _
"([OddCogTotDefVal], [EvenCogTotDefVal], [GPETETotalDefVal], [IntOddTotDefVal], [IntEvenTotalDefVal], [RepOddTotDefVal], [RepEvenTotDefVal])" & _
"VALUES(" & _
"" & Me.txtOddCogTotalDefVal.Value & "," & _
"" & Me.txtEvenCodTotaDefVal.Value & "," & _
"" & Me.txtRepairCogTotalDefVal.Value & "," & _
"" & Me.txtInitialOddCogDefVal.Value & "," & _
"" & Me.txtInitialIssueEvenCogDef.Value & "," & _
"" & Me.txtReplacementIssueOddCogDef.Value & "," & _
"" & Me.txtReplacementIssueEvenCogDef.Value & "," & _
")"
DoCmd.RunSQL strquery
MsgBox "Deficit values have been added"
End Sub

The problem is I keep getting an error, error number 3134 (Syntax Error) on the docmd.runsql portion. I have double checked everything and there are no mispellings, so thats not it. And the punctuation is the same as another VBA code on another form, only the fields and tables have changed. I cant figure out whats wrong. Please help.

Dare I suggest something as trivial as having a space before VALUES ? On the line give it a breathing room like so :

" VALUES(" & _

Best,
Jiri

 

[Artemis Hothmire]

None odf your suggestions are working....i keep getting a error statement. a syntax error. i have tried all of your examples, and nothing is working. It always stops at the same point, the docmd.runsql. What gets me the most frustrated is that this similar code works on anorther form....what am i doing wrong?

 

[michaeljryan78]

what did you see in the immediate window on the debug.print? If you see something there, copy it, paste into a query in SQL view and try to run. If there is an error, access should place a cursor in the SQL at that pont.

 

[Artemis Hothmire]

Micheal J Ryan, your a freaking lifesaver!!!! It was the damned null value. I couldn't understand it at first, but because there was a null value, I had to many commas. I have to figure out how to make that null value a 0 if nothing is going to appear. At least that way I have some data to put into my tables, and later on graphes. Thank you so much man, your badass!!

 

[michaeljryan78]

try NZ(fieldname, 0 ) wrap all firm field names in that like NZ(Me.txtOddCogTotalDefVal.Value, 0)

 

[Artemis Hothmire]

I have created the following parameters for the form control:

str = "SELECT * FROM qryAnnualMidYearBudgetReviewRepairableCOGTotalDeficits"
Set rs = db.OpenRecordset(str)
If rs.EOF = False Then
Me.txtRepairCogTotalDefVal = rs![sumofTotalDeficit]
ElseIf Me.txtRepairCogTotalDefVal = Null Then
Me.txtRepairCogTotalDefVal = [$0.00]
End If
rs.Close

but the zeros do not show up once i run the sub. where would the NZ(Fieldname,0) be placed?

 

[michaeljryan78]

Me.txtRepairCogTotalDefVal = NZ(rs![sumofTotalDeficit],0)

 

[michaeljryan78]

what Fields have potential NULLS for the Insert statement? are you looking to have the zeros in Me.txtRepairCogTotalDefVal?

 

[Artemis Hothmire]

never mind...i found it...Thanks agian Michael!

 

[Ctimson]

Hi guys - I have a problem with my simple insert statement! It gives me the run error of 3134.

The code:

Private Sub cmdadd_Click()

CurrentDb.Execute "INSERT INTO Crit_Code(crit_code, model/year, launch/series/mixed)VALUES (" & Me.CritCode & ", " & Me.model & ", " & Me.launch & ");"

End Sub