Running an Update query within VBA

[Magnetar]

Hello
Could someone please help with what should be a very 'simple' procedure?(!!).

The update qry works perfectly within the query grid, ie writes the rq'd info into one tbl, based on data from another tbl. However - need to run this within VBA, (on user clicking button from front-end form).

Initally the following was used:

DoCmd.RunSQL " UPDATE tblA
INNER JOIN tblB '_
ON tblA.FileX = tblB.FileX '_
SET tblB.CalcDate2 = DateAdd("m",9,[tblA].[Date1]), '_
tblB.CalcDate3 = DateAdd("m",15,[tblA].[Date1]);"


Then, following 'nickjar2''s response to LizardKingSchwing this morning, I used the following alternative method:



Dim CalcDateSQL As String
Dim db As Database

Set db = CurrentDb
CalcDateSQL = "UPDATE tblA
INNER JOIN tblB '_
ON tblA.FileX = tblB.FileX '_
SET tblB.CalcDate2 = DateAdd("m",9,[tblA].[Date1]), '_
tblB.CalcDate3 = DateAdd("m",15,[tblA].[Date1]);"

db.Execute (CalcDateSQL)
Set db = Nothing
db.Close


On both occasions, I receive the error msge:

"Compile error:

Expected: end of statement"

This should be very trivial to solve, but for the life of me, I cannot see the error!

(I DO need to run this qry from the front-end form, to populate a text field with a calculated date. I don't wish to change the query, as it works fine).

Please help

Cheers, Magnetar

 

[genomon]

Hi Magnetoman!
Try removing the semicolon from the end of your SQL string - needed in the query, but not in code.
-Genomon

 

[jitter]

Hmmm...I see a couple of things

1. you don't need the parantheses in:
db.Execute (CalcDateSQL)

it should just be:

db.Execute CalcDateSQL

2. you need to change the order of the following lines.


Set db = Nothing
db.Close


is should be:


db.Close
Set db = Nothing


3. you need to be sure to use triple double quotes where you actually want a quote to appear within a quote.

ie.


SET tblB.CalcDate2 = DateAdd("m",9,[tblA].[Date1]), _
tblB.CalcDate3 = DateAdd("m",15,[tblA].[Date1]);"


would become


SET tblB.CalcDate2 = DateAdd("""m""",9,[tblA].[Date1]), _
tblB.CalcDate3 = DateAdd("""m""",15,[tblA].[Date1]);"



I hope some of this helps with your problem.

 

[jitter]

ooops...that would be two double quotes where you want a quote

SET tblB.CalcDate2 = DateAdd(""m"",9,[tblA].[Date1]), _
tblB.CalcDate3 = DateAdd(""m"",15,[tblA].[Date1]);"

 

[datawise]

I define and save the queries separately, so that they can be updated by non programmers and then run the following code.

dim qry as querydef
DoCmd.SetWarnings False
Set qry = db.QueryDefs("qryUpdateQueryName&quot Input table
qry.Execute
DoCmd.SetWarnings True

Hope this helps

Peter