Suppress msg from update query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

myxmaster@hotmail.com - 30 Apr 2007 16:36 GMT

I would like to run this update query automatically when the database
opens. At present I have it running from a macro when the form is
opened however it prompts the user twice before it runs. I would like
to eliminate the user input.

UPDATE Transactions SET Transactions.Status = "Cleared"
WHERE (((Transactions.date)<Date()-10) And
((Transactions.Status)="Pending"));

TIA

Reply to this Message

Douglas J. Steele - 30 Apr 2007 16:46 GMT

Dim strSQL As String

strSQL = "UPDATE Transactions " & _
"SET Transactions.Status = 'Cleared' " & _
"WHERE Transactions.[Date]<Date()-10 " & _
"AND Transactions.Status='Pending'"

CurrentDb.Execute strSQL, dbFailOnError

Note that having a field named Date in your table is not a good idea: Date
is a reserved word, and using reserved words for your own purposes can lead
to problems. If you cannot (or will not) rename the field, at least enclose
it in square brackets, as I've done above.

For a good discussion of what words to avoid, check what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I would like to run this update query automatically when the database
> opens. At present I have it running from a macro when the form is
[quoted text clipped - 6 lines]
>
> TIA

Reply to this Message

myxmaster@hotmail.com - 30 Apr 2007 17:07 GMT

On Apr 30, 8:46 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> Dim strSQL As String
>
[quoted text clipped - 32 lines]
>
> - Show quoted text -

Thanks for the quick reply Doug, however where would I place this code?

Reply to this Message

Douglas J. Steele - 30 Apr 2007 17:34 GMT

In the Open event of the first form that starts up would be one place.

Of course, do you REALLY want it running each time the form is opened?
Should you perhaps keep track of whether or not it's run today, and only run
it if it hasn't already been run?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> On Apr 30, 8:46 am, "Douglas J. Steele"
>
[quoted text clipped - 37 lines]
>> > WHERE (((Transactions.date)<Date()-10) And
>> > ((Transactions.Status)="Pending"));

Reply to this Message

myxmaster@hotmail.com - 30 Apr 2007 19:50 GMT

On Apr 30, 9:34 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> In the Open event of the first form that starts up would be one place.
>
[quoted text clipped - 53 lines]
>
> - Show quoted text -

Doug, I dont know what I was thinking, there obviously is no need to
run the procedure everytime the form is opened. How would you suggest
to put a time element on the procedure? say daily

TIA

Reply to this Message

Douglas J. Steele - 30 Apr 2007 22:15 GMT

You could create a table (for the sake of argument, call it "UpdateControl")
that has a single field in it "LastUpdated". Put a single row in that table,
with today's date.

Before you run the query, check when the table was last updated, and only
run the code if it was last updated prior to today. When you do run the
update query, update your table:

Dim strSQL As String

If Nz(DLookup("LastUpdate", "UpdateControl"), #1/1/1970#) < Date() Then

strSQL = "UPDATE Transactions " & _
"SET Transactions.Status = 'Cleared' " & _
"WHERE Transactions.[Date]<Date()-10 " & _
"AND Transactions.Status='Pending'"
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "UPDATE UpdateControl " & _
"SET LastUpdated = " & Format(Date(), "\#mm\/dd\/yyyy\#") & _
CurrentDb.Execute strSQL, dbFailOnError

End If

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Doug, I dont know what I was thinking, there obviously is no need to
> run the procedure everytime the form is opened. How would you suggest
[quoted text clipped - 52 lines]
>> >> > WHERE (((Transactions.date)<Date()-10) And
>> >> > ((Transactions.Status)="Pending"));- Hide quoted text -

Reply to this Message