Run Code on startup of database

[jeffshex]

Is this possible?

I'm wanting to run an if statement to look at a few different fields in multiple tables and change some values if certain conditions hold true.

If it can be done, what should I be looking for?

Ideas?

 

[randy700]

If you create a macro, and name it Autoexec, it will run at startup.


Randy

 

[TomCologne]

One way to do that is to use a startup form (Tools>Startup...) which can be invisible and put your code behind it.

TomCologne

 

[jeffshex]

That was one of my guesses.

I just didn't know if there was something you could write in a module that runs on startup.

 

[randy700]

I just didn't know if there was something you could write in a module that runs on startup.

Like an Autoexec macro?


Randy

 

[jeffshex]

I guess so.

For example, each form has their On Load event.
I was hoping that there was a database on load event.

That way I could just make a module that would contain that code. Then it would be independent of any forms.

I'd like to avoid macros all together if possible.

Don't know if I'm wishing or what.

 

[randy700]

If you don't want to use a macro, use a startup FORM and put your code in the OnOpen event. I personally NEVER use macros, except for Autoexec. It comes in very handy when I have code that I want to run before the user ever sees anything in the application.


Randy

 

[jeffshex]

Ok, now it's just a matter of figuring out how to run the code.

 

[randy700]

I'm wanting to run an if statement to look at a few different fields in multiple tables and change some values if certain conditions hold true.


You might look at the DLookup method.
Something like...

If (DLookup("values", "yourTable", "criteria") = "certainCondition") Then
   DoCmd.OpenQuery "UpdateQuery"
End If

Randy

 

[jeffshex]

Not sure if this is the best way...but it works.

Dim SQLFollowUp As String
Dim SQLNoCommunication as String

SQLFollowUp = "UPDATE tblCustomers INNER JOIN tblBid ON tblCustomers.CustomerID = tblBid.CustomerID " & _
        "Set tblCustomers.CustomerType = 'Follow-up' " & _
        "WHERE tblBid.BidDate <= Date()-30 and tblCustomers.CustomerType = 'Bid/Estimate'"

SQLNoCommunication = "UPDATE tblCustomers INNER JOIN tblBid ON tblCustomers.CustomerID = tblBid.CustomerID " & _
        "Set tblCustomers.CustomerType = 'No Communication' " & _
        "WHERE tblBid.BidDate <= Date()-30 and tblCustomers.CustomerType = 'Follow-up'"
   
DoCmd.RunSQL SQLFollowUp
DoCmd.RunSQL SQLNoCommunication

 

[randy700]

Looks good. Where do you have it?


Randy

 

[jeffshex]

I have an autoexec that launces a form, so I just popped it in that forms On Load event.

-Jeff

 

[randy700]

Hmmm... I thought you were opposed to any macros.

Placing the code in the On Load event of the form should work fine. However, since you now have the Autoexec, why not run this code BEFORE the form opens/loads?


Randy

 

[jeffshex]

I apologize...it's not an autoexec macro.

In tools > Startup...
You can choose a form to open up automatically, so that's where it's coming from.