Added a field to the SQL Insert within VBA and the Insert fails now (1 Viewer)

dcavaiani

Registered User.
Local time
Today, 05:34
Joined
May 26, 2014
Messages
385
The insert into table field name is desc and it is loaded from the "input" table with a field named description (I know it's a reserved name now) So in the vba I refer to that source table field as [description] - but still errors out on the SQL Insert?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:34
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you show us the code you're using? Thanks.
 

June7

AWF VIP
Local time
Today, 02:34
Joined
Mar 9, 2014
Messages
5,425
Works for me.

What is error message?

Provide your SQL statement or your database for analysis.
 

dcavaiani

Registered User.
Local time
Today, 05:34
Joined
May 26, 2014
Messages
385
{code}
Private Sub Command4_Click()

On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rstholdings As DAO.Recordset

Dim savaccount As String
Dim savsymbol As String
Dim savdescription As String
Dim straccount As String
Dim strsymbol As String
Dim strBegin As String
Dim strEnd As String
Dim savEnd As String
Dim strBPrice As Currency
Dim strEPrice As Currency
Dim savEPrice As Currency
Dim strBShares As String
Dim strEShares As String
Dim savEShares As String
Dim strBClose As Currency
Dim strEClose As Currency
Dim savEClose As Currency
Dim strDesc As String
Dim savDesc As String

Set db = CurrentDb
Set rstholdings = db.OpenRecordset("holdings")
' straccount = "BMENT_Roth"

' DoCmd.OpenForm "RickChangeTimes", , , "dated = [Pick 1 Date, Format: 05/13/16]"
' Let's clear out the file first
' With CurrentDb
' .Execute "DELETE * FROM sheet4errors;", dbFailOnError
' Debug.Print .RecordsAffected & " in error"
' End With

rstholdings.MoveFirst

nextset:

straccount = rstholdings!account
strsymbol = rstholdings!symbol
strBegin = rstholdings!dated
strEnd = rstholdings!dated
strBPrice = rstholdings!lastprice
strEPrice = rstholdings!lastprice
strBShares = rstholdings!shares
strEShares = rstholdings!shares
strBClose = rstholdings!lastclose
strEClose = rstholdings!lastclose
strDesc = rstholdings![Description]
' Debug.Print straccount, strsymbol, strdated; strlastprice
' rstholdings.MoveNext 'record #2
' rstholdings.MoveNext 'Record #3
' rstholdings.MoveNext 'Record #4
' strvalue = rstholdings!dated
' DoCmd.RunSQL "INSERT INTO hoursdetails (cust, job, dated, dow, empl, ami, amo, amhrs, pmi, pmo, pmhrs, total) VALUES(""" & [pickcustomer].Column(0) & """, """ & [pickcustomer].Column(1) & """, """ & dated & """, """ & Dow & """,""" & Empl & """, " & AMI & ", " & AMO & ", " & AMHrs & ", " & PMI & ", " & PMO & ", " & PMHrs & ", " & Total & ");"
'DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO Performance (account, symbol, [begin], [end], bprice, eprice, bshares, eshares, bclose, eclose, desc) VALUES(""" & straccount & """, """ & strsymbol & """, """ & strBegin & """, """ & strEnd & """, """ & strBPrice & """, """ & strEPrice & """, """ & strBShares & """, """ & strEShares & """, """ & strBClose & """, """ & strEClose & """, """ & strDesc & """);"
savaccount = rstholdings!account
savsymbol = rstholdings!symbol
savDesc = rstholdings![Description]
skipsome:
rstholdings.MoveNext ' Bring up next record

If rstholdings.EOF Then ' Need to do the last record UPDATE!
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Performance set [end] = """ & savEnd & """, eprice = """ & savEPrice & """, eshares = """ & savEShares & """, eclose = """ & savEClose & """ where account = """ & savaccount & """ and symbol = """ & savsymbol & """ and desc = """ & savDesc & """;"
End If

If rstholdings!account = savaccount And rstholdings!symbol = savsymbol Then
savEnd = rstholdings!dated
savEPrice = rstholdings!lastprice
savEShares = rstholdings!shares
savEClose = rstholdings!lastclose
GoTo skipsome
Else

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Performance set [end] = """ & savEnd & """, eprice = """ & savEPrice & """, eshares = """ & savEShares & """, eclose = """ & savEClose & """ where account = """ & savaccount & """ and symbol = """ & savsymbol & """ and desc = """ & savDesc & """;"
End If

GoTo nextset

' MsgBox strCustName & " " & strAMI & " " & strAMO & " " & strdate & " " & strempl

' Debug.Print strdate, strempl, strCustName, strCustName2, strAMI, strAMO, strPMI, strPMO, strAMI2, strAMO2, strPMI2, strPMO2

'_________________________________
On Error GoTo ErrorHandler
ErrorHandlerExit:
DoCmd.Close acForm, Me.Name
Exit Sub
ErrorHandler:
If Err = 3021 Then ' no current record
Else
' MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

End Sub
{/code}
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:34
Joined
Oct 29, 2018
Messages
21,358
Have you tried enclosing the reserved field name in square brackets?
 

dcavaiani

Registered User.
Local time
Today, 05:34
Joined
May 26, 2014
Messages
385
Code all worked before I tried to add this last field.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:34
Joined
Oct 29, 2018
Messages
21,358
No, same end of VBA on first INSERT INTO attempt
Sorry, I don't understand this answer. I don't see any square brackets in the first INSERT INTO statement; and in the second one, I only see it in [begin] and [end], but not in [desc].
 

dcavaiani

Registered User.
Local time
Today, 05:34
Joined
May 26, 2014
Messages
385
Sorry, I don't understand this answer. I don't see any square brackets in the first INSERT INTO statement; and in the second one, I only see it in [begin] and [end], but not in [desc].
Does desc need it too ?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:34
Joined
Feb 19, 2002
Messages
42,981
When you are building embedded SQL strings, ALWAYS build them into a variable and use the variable in the execute statement. This gives you a debugging tool. Once the string is built, you can print it to the immediate window where you may see the syntax error or you can copy the string as printed in the IW and paste it into the SQL view of the QBE and run it there. You frequently get better error messages.
 

Users who are viewing this thread

Top Bottom