Running Several SQL Update Queries

[adammoo1984 via AccessMonster.com]

Hi Guys,

Currently having issues trying to get the following to work:

Private Sub Label11_Click()

DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Stores INNER JOIN Actions ON Stores.[Store No] = Actions.
[Store Number] SET Actions.[Date Works Required] = (Forms![Rebalance Update
Popup]![Start Date]-167) WHERE (((Actions.[Store Number])=Forms![Rebalance
Update Popup]![Store Number]) AND ((Actions.Action)=""RGIS Fee ordered""));"
MsgBox "All dates are now updated. Please check to see if any completed
tasks need re-doing (e.g. profile packs)", vbOKOnly, "Complete"
DoCmd.SetWarnings True
DoCmd.Echo True

End Sub

There are actually 12 update queries but they all follow the same format as
above. I need to take the dates which are shown on the form and change to
the date shown on a popup form +/- a number of days. Whenever I run this
currently it just clears all the dates on the form.

Anyone any ideas or questions? I'm a learner so be gentle.

Thanks,
Adam.

 

[Alex Dybenko]

Hi,
try to make a select query of it, add Forms![Rebalance Update
Popup]![Start Date]-167) on select list and check what it returns

you can also consider replacing reference to control with it actual value
and put RGIS Fee ordered value in single quotes:

DoCmd.RunSQL "UPDATE Stores INNER JOIN Actions ON Stores.[Store No] =
Actions.
[Store Number] SET Actions.[Date Works Required] = #" &
format(Forms![Rebalance Update Popup]![Start Date]-167,"mm\/dd\/yyyy") & "#
WHERE (((Actions.[Store Number])=" & Forms![Rebalance Update Popup]![Store
Number] & ") AND ((Actions.Action)='RGIS Fee ordered'));"


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[adammoo1984 via AccessMonster.com]

Hi,

I tried the second option you gave and it worked wonderfully, That is until
I tried to run it for a second time. The first run altered all the dates,
the second run deleted them all.

Any ideas folks?

Ta,
Adam.

Hi,

Number] & ") AND ((Actions.Action)='RGIS Fee ordered'));"

Hi Guys,

[quoted text clipped - 28 lines]

Thanks,
Adam.

 

[John Spencer]

At a minimum I would rewrite the sub as follows.

Assumptions:
Store number is a text value
you are using dates in the US format of mm-dd-yy.

If your dates are in some other format, you will need to use
Format(DateAdd(...),"yyyy-mm-dd") to ensure your date is being properly handled.

Private Sub Label11_Click()
Dim StrSQL as String

'Check values to make sure something is there
IF IsDate(Forms![Rebalance Update Popup]![Start Date]) = False Then
Msgbox "You must set a Start Date"

ELSEIF Len(Forms![Rebalance Update Popup]![Store Number] & "") = 0 Then
MSGBox "You must set a Store Number"
ELSE

DoCmd.Echo False
DoCmd.SetWarnings False
StrSQL = "UPDATE Stores INNER JOIN Actions " & _
" ON Stores.[Store No] = Actions.[Store Number]" & _
" SET Actions.[Date Works Required] = #" & _
DateAdd("D",-167,Forms![Rebalance Update Popup]![Start Date])
"# WHERE Actions.[Store Number]= """ & _
Forms![Rebalance Update Popup]![Store Number] & _
""" AND Actions.Action=""RGIS Fee ordered"" "
'Temporarily add the following line so you can check the accuracy
'of the SQL statement. Also, Temporarily comment out the
'DoCmd.Echo False and docmd.SetWarnings False lines

Debug.Print StrSQL : STOP

DoCmd.RunSQL StrSQL

MsgBox "All dates are now updated. Please check to see if any completed
tasks need re-doing (e.g. profile packs)", vbOKOnly, "Complete"
END IF

DoCmd.SetWarnings True
DoCmd.Echo True

END SUB

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[adammoo1984 via AccessMonster.com]

Guys,

I managed to simplify this. In the end I moved the controls which were on
the popup form into the header of the main form. I then discovered that the
issue was because the only active control on my popup was the date box, when
I clicked to run the SQL it wasn't classing it as being "updated". I then
solved this when I moved it into the form header by setting the first task on
the button to move focus to a seperate control.

Cheers Guys, no dooubt post again soon!

Adam

 

[Tony Toews [MVP]]

DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Stores INNER JOIN Actions ON Stores.[Store No] = Actions.
[Store Number] SET Actions.[Date Works Required] = (Forms![Rebalance Update
Popup]![Start Date]-167) WHERE (((Actions.[Store Number])=Forms![Rebalance
Update Popup]![Store Number]) AND ((Actions.Action)=""RGIS Fee ordered""));"
MsgBox "All dates are now updated. Please check to see if any completed
tasks need re-doing (e.g. profile packs)", vbOKOnly, "Complete"
DoCmd.SetWarnings True
DoCmd.Echo True

I would also remove the docmd.Echo and the setwarnings lines and
change runSQL to currentdb.execute.

The problem with DoCmd.RunSQ is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[adammoo1984 via AccessMonster.com]

Tony,

I didn't really want to turn the warnings off but as there are 12 update
queries to run in total it just wasn't feasible for the user to have to press
ok that many times. Any ideas around this.

Ta,
Adam.

 

[Tony Toews [MVP]]

I didn't really want to turn the warnings off but as there are 12 update
queries to run in total it just wasn't feasible for the user to have to press
ok that many times. Any ideas around this.

Using currentdb.execute means that the action query messages "Click OK
to add/update records" or whatever it is don't come up. And error
messages do happen.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

If using DAO, use Currentdb.Execute strSQL,dbfailonerror

And always, always, always be sure that you only use dbFailOnError
with an error handler in effect.

It's quite easy to write replacement for DoCmd.RunSQL that uses
..Execute and dbFailOnError and has an error handler so that you only
have to write the error handler the one time.

Hint, hint.

 

[Tony Toews [MVP]]

Hint, hint.

<chuckle> OK, ok, I'll try to remember that.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

<chuckle> OK, ok, I'll try to remember that.

I was just begin coy -- I wrote the function a million years ago:

Public Function SQLRun(strSQL As String) As Boolean
On Error GoTo errHandler

dbLocal.Execute strSQL, dbFailOnError
RunSQL = True

exitRoutine:
Exit Function

errHandler:
MsgBox err.Number & ": " & err.Description, vbExclamation, _
"Error in SQLRun()"
Resume exitRoutine
End Function

I'm not sure if you might want to put some checking in there to make
sure the SQL is not a SELECT or just let the error handler deal with
it. I'm not certain there's a reliable way to detect an action query
(since parameters might be declared at the beginning of the SQL
statement), so it's probably just easier to let the error handler
deal with it (though it bugs me to let an error handler handle an
error that I've already anticipated).