Using VB to do Sql insert into an Access table

[eratem]

Getting Syntax error using VB to insert into an Access table.

This code is in a loop.
The Division is set to a value pulled from a database
The Prior_Division stores the previous value for the Division varaible


Dim Division As String
Dim Prior_Division As String

SQL_Insert =
"Insert into TABLE_A (DateRun,Source,Period,Division)
values
(# " & Now & " #, ""SRC1"", ""2011"", & Prior_Division & );"


I am getting Syntax error (missing operator) in query expression '&Prior_Division &'
I've done debug statements and printed the values for the Division and Prior_Division variables and
they are simple text values like "Dept A" "Dept B"

Any help on why I get the error is appreciated

[orange]

Do a debug.print on sql_Insert

[eratem]

SQL_Insert = "Insert into HERA_Ownership (DateRun,Source,Period,Division,) values (# " & Now & " #, ""HQ"", ""2011"", " & Division & ");"

when I do a debug statement to show the sql string, here is the string value
Insert into table (DateRun,Source,Period,Division) values (# 3/21/2012 10:20:22 AM #, "HQ", "2011", DEPT A)
how do I format the Insert statement to allow " & Division & " to be a string?
It looks like it's just using the raw text value and giving me a sql error

[orange]

Where do you assign a value to Division (and Prior_Division)?

[eratem]

Division is pulled from an Access Table. The values are like "Department A", "Department B", etc.
Prior_Division stores the value of Division before it's pulled from the database and then it compares Division to Prior_Division to see if the value changed.

[orange]

Division is pulled from an Access Table. The values are like "Department A", "Department B", etc.
Prior_Division stores the value of Division before it's pulled from the database and then it compares Division to Prior_Division to see if the value changed.

But none of that info is in your SQL_Insert statement. Somehow you have to assign or associate values in that Access table to your SQL_Insert statement.

[eratem]

Here is the full code...

Option Compare Database
Private Sub CalculateDivOwn_Click()
On Error GoTo Err_CalculateDivOwn_Click

Dim SQL_Insert As String

Dim Division As String
Dim Prior_Division As String
Dim AASum As Double
Dim AnAmSum As Double

Dim RS As DAO.Recordset

Set RS = CurrentDb.OpenRecordset("H2011Data")

Division = "INITIAL"
'Open the Access Table
DoCmd.OpenTable "H2011Data"

DoCmd.GoToRecord , , acFirst

Do While Not RS.EOF

' Set variables to each field in the Access Table

Prior_Division = Division
Division = RS![DIVISION_DESCR]

If Division <> Prior_Division And Prior_Division <> "INITIAL" Then
' MsgBox "Prior Division: " & Prior_Division
' MsgBox "Division: " & Division
DoCmd.SetWarnings False
SQL_Insert = "Insert into HERA_Ownership (DateRun,Source,Period,Division) values (# " & Now & " #, ""HQ"", ""2011"", " & Prior_Division & ");"

MsgBox "SQL:" & SQL_Insert

DoCmd.RunSQL SQL_Insert
DoCmd.SetWarnings True

AASum = 0
AnAmSum = 0
End If

AA = RS![AA]
AnAm = RS![AnAm]

RS.MoveNext

Loop
RS.Close
DoCmd.Close

[orange]

What exactly are you trying to do?
What is the definition of this Table?
This statement

Set RS = CurrentDb.OpenRecordset("H2011Data")

will open the Table and position at the First record.

So these lines

'Open the Access Table
DoCmd.OpenTable "H2011Data"

DoCmd.GoToRecord , , acFirst

are redundant (You could comment them out in my view)


I think these lines

AASum = 0
AnAmSum = 0

don't do anything.

You might want to just Debug.print your sql_insert instead of

DoCmd.RunSQL SQL_Insert

until you get rid of the syntax error and check your logic.
Also add a message box to see what's in Division and Prior_Division and step through the code.

You should include Error handling

You may wish to investigate

Currentdb.execute yourSQL string,dbFailonError as an alternative to your

Setwarning; DoCmd.runsql; Setwarnings

Good luck

[eratem]

Thanks for your help Orange. Based on your comments I will clean up my code.

I was able to finally figure out the variable issue with the quotes.
I needed to add triple double quotes around the variable and that worked...

SQL_Insert = "Insert into TableA (DateRun,Source,Period,Division) values (# " & Now & " #, ""AA"", ""2011"", """ & Division & """);"

thanks again for all your help.