By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,561 Members | 3,221 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,561 IT Pros & Developers. It's quick & easy.

SQL Update Query (Running in VBA)

100+
P: 153
Good morning fellow programmers.

Just a quick question I hope...

I'm trying to run some SQL from VBA and I'm getting an error....I think it's Syntax?

This is the code:

[PHP] Dim stSQL2

stSQL2 = "UPDATE [AddedToOutlook] INNER JOIN [tblContracts] ON [AddedToOutlook].DatabaseReferenceNumber = [tblContracts].DatabaseReferenceNumber" & _
"Set [AddedToOutlook].AddedToOutlook = False" & _
"Where [tblContracts].RenewalDate < current_date"

DoCmd.RunSQL stSQL2[/PHP]

I'm basically trying to use SQL to sort of set a primary key (the numbers actually directly correspond, but due to some things I'm doing with the VBA code I can't set a primary key)...anyways based on that "primary key" wherever the field Renewal date is less than the current date, I want to set the boolean field "AddedToOutlook" in the table AddedToOutlook (I know I should have called it tblAddedToOutlook) equal to False because I then have the program change the date and it's now recognized as a new entry (although the database reference number remains the same).
Jan 9 '07 #1
Share this Question
Share on Google+
5 Replies


PEB
Expert 100+
P: 1,418
PEB
I think no space btwn DatabaseReferenceNumberSet [AddedToOutlook].AddedToOutlook = FalseWhere [tblContracts].RenewalDate < current_date"

and missing ";"

The Inner joins aren't my favorite thing in Updates...

But this is only at first view!
Jan 9 '07 #2

100+
P: 153
hhmm well perhaps...is there a way that I can take something out of the VBA like for example...I'll run a loop and find out, in vba, where renewaldate < date and each time that is true (in an if statement nested in an until .EOF loop), then it will run SQL based on that coordinate? like where renewaldate<date then update At [AddedToOutlook].(recSet1.Fields("DatabaseReferenceNumber"))??

I know you can do something like Forms!UserForm!txtFilterMainName and take an actual value from a form...so I would assume you can carry over a value from your VBA code somehow, as well?
Jan 9 '07 #3

100+
P: 153
Actually...since the program adds new entries to AddedToOutlook, based on the above code...if I could do something like that...I guess it would be easier to do something along the lines of Delete FieldAddedToOutlook From [AddedToOutlook] Where [AddedToOutlook].(recSet1.Fields("DatabaseReferenceNumber"))?
Jan 9 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
Good morning fellow programmers.

Just a quick question I hope...

I'm trying to run some SQL from VBA and I'm getting an error....I think it's Syntax?

This is the code:

[PHP] Dim stSQL2

stSQL2 = "UPDATE [AddedToOutlook] INNER JOIN [tblContracts] ON [AddedToOutlook].DatabaseReferenceNumber = [tblContracts].DatabaseReferenceNumber" & _
"Set [AddedToOutlook].AddedToOutlook = False" & _
"Where [tblContracts].RenewalDate < current_date"

DoCmd.RunSQL stSQL2[/PHP]

I'm basically trying to use SQL to sort of set a primary key (the numbers actually directly correspond, but due to some things I'm doing with the VBA code I can't set a primary key)...anyways based on that "primary key" wherever the field Renewal date is less than the current date, I want to set the boolean field "AddedToOutlook" in the table AddedToOutlook (I know I should have called it tblAddedToOutlook) equal to False because I then have the program change the date and it's now recognized as a new entry (although the database reference number remains the same).
The ( ; ) is not necessary but the spaces before Set & Where are crucial (normally placed at the end of the preceeding lines).
Expand|Select|Wrap|Line Numbers
  1.     stSQL2 = "UPDATE [AddedToOutlook] INNER JOIN [tblContracts] ON [AddedToOutlook].DatabaseReferenceNumber = [tblContracts].DatabaseReferenceNumber " & _
  2.              "Set [AddedToOutlook].AddedToOutlook = False " & _
  3.              "Where [tblContracts].RenewalDate < Date()"
Jan 9 '07 #5

100+
P: 153
Ahh okay. Thanks.

I actually made up a way to do this in perhaps a very strange way lol.

I knew you could take fields form the form into a query but I didn't know if you could take anything from the VBA code behind the form.

So I created a text box that wasn't visible called txtReference and used that to do the following query:

DELETE *
FROM AddedToOutlook
WHERE AddedToOutlook.DatabaseReferenceNumber=Forms!UserF orm!txtReference;


Perhaps a bit messy...but at least when I leave they'll still need me for consulting :) lol
Jan 9 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.