SQL Update Query (Running in VBA)

[Kosmos]

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).

[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!

[Kosmos]

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?

[Kosmos]

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"))?

[NeoPa]

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).

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

[Kosmos]

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