asked on

How run update query on database startup

I have an update query that I want to run on database startup but I don't want the update message to appear. I've tried creating an AutoExec macro with the query in it but can't avoid the message.

I may be better with VBA code running on startup so the setwarnings can be false but I don't know how to write the code.

The update query SQL is:

UPDATE tblCustomers SET tblCustomers.SubscriptionExpired = True, tblCustomers.CustomerStatus = 2
WHERE (((tblCustomers.CurrentExpirationDate)<Now()));

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

How would I use this code in an AutoExec macro? Or would I just have it in the onopen event of my starting form?

Jim Dettman (EE MVE)

<<How would I use this code in an AutoExec macro? Or would I just have it in the onopen event of my starting form? >>

You can do it a couple different ways:

1. Use the RunCode Action in the Autoexec macro (procedure must be a function, not a sub and it doesn't matter if it returns anything).

2. Use a form and as you guessed, place the call to the code (or the code itself) in the OnOpen event.

Jim.

Jim Dettman (EE MVE)

BTW, you might want to take 5 minutes and look at this:

https://www.experts-exchange.com/videos/537/MS-Access-Different-Ways-to-Start-Up-a-Database.html

Not quite what your asking about, but does pertain to it.

Jim.

SteveL13

ASKER

Jim,

Very well done video. I'll hang onto that .

So I have created a public function and here is the code:

Public Function UpdateRecords()

    Dim strSQL As String
        strSQL = "UPDATE tblCustomers SET tblCustomers.SubscriptionExpired = True, tblCustomers.CustomerStatus = 2 WHERE tblCustomers.CurrentExpirationDate)<Now()"
    CurrentDb().Execute strSQL, dbFailOnError
    
End Function

Open in new window

But when the database opens I get a syntax error on the line:

CurrentDb().Execute strSQL, dbFailOnError

I have also attached a screen shot of the AutoExec in case I have that messed up.

SteveL13

ASKER

And here is the error message...

SteveL13

ASKER

I found it. It was the extra ) in the line. Thank you very much.

Jim Dettman (EE MVE)

Great. Glad to hear it's working. Now just add some error handling to that so it catches any errors (On Error Goto ...)

Jim.