Suppress msg from update query

[myxmaster]

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

 

[Douglas J. Steele]

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

 

[myxmaster]

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 athttp://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVPhttp://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
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- Hide quoted text -

- Show quoted text -

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

 

[Douglas J. Steele]

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?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

On Apr 30, 8:46 am, "Douglas J. Steele"

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

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
athttp://www.allenbrowne.com/AppIssueBadWord.html

--
Doug Steele, Microsoft Access MVPhttp://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
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"));

 

[myxmaster]

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?

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

On Apr 30, 8:46 am, "Douglas J. Steele"

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

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
athttp://www.allenbrowne.com/AppIssueBadWord.html
--
Doug Steele, Microsoft Access MVPhttp://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
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"));- Hide quoted text -

- 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

 

[Douglas J. Steele]

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




--
Doug Steele, Microsoft Access MVP

(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
to put a time element on the procedure? say daily

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?

On Apr 30, 8:46 am, "Douglas J. Steele"

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

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
athttp://www.allenbrowne.com/AppIssueBadWord.html

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"));- Hide quoted text -