Update form problem 2

[mama16]

I have this query,
UPDATE Deployers SET Deployers.Months_Operational = Deployers.Months_Operational+1;

At the beginning of every month I run it and it increments the months by one.
Is there a way where I can set it so it does it automatically in the background
Can a macro do it?

Thanks

 

[TheAceMan1]

How are ya mama16 . . . . .

Since this task occurs once monthly, a [blue]macro ([purple]AutoExec[/purple]) which runs when the database is opened[/blue] would be perfect. As I started writing the code, [blue]three problems came to mind that had to be solved[/blue]:
[ol][li][purple]What to do if the database is not opened on the 1st[/purple] (maybe its vacation time), to keep you from manually updating?[/li]
[li]If the database is opened & closed several times on the 1st, [purple]how to update only once?[/purple][/li]
[li][purple]A reference date[/purple] (containing last updated month) [purple]needs to be saved in the database[/purple] for comaprison. Where to save it?[/li][/ol]

To solve the first problem, I designed the code to [purple]update when a change in month is detected[/purple]. [blue]So updating will occur [purple]the first time[/purple] you open the database after the month has changed[/blue] (usually on the 1st)!

For the 2nd & 3rd problems, when an update occurs the date is saved/updated in a table. [blue]As long as the month of the saved date equals the month of the current date, no updating occurs![/blue]

OK . . . . lets do this . . . . ([purple]backup the database so you can come back to square one if you have to[/purple])

In the Macros window open a new macro and make it look like the following:

[blue]Macro Name    Action    Function Name
----------   --------   -------------
             RunCode    IncMonths()
             StopMacro[/blue]

Save & Name the macro [purple]AutoExec[/purple] (spelling is important).

Next, [blue]make a new table[/blue] with the following properties:
[blue]Field Name [purple]LastDate[/purple]
Data Type [purple]Date/Time[/purple]
Format [purple]Short Date[/purple][/blue]
Save & name the table [purple]tblAutoDate[/purple]. Answer [purple]No[/purple] to primary key when you save!

Finally, in a module in the modules window, copy/paste the following function:

[blue]Public Function IncMonths()
   Dim refDate As Date, SQL As String
   
   refDate = DLookup("[LastDate]", "tblAutoDate")
   
   If Month(Now()) <> Month(refDate) Then
      SQL = "UPDATE Deployers " & _
            "SET Months_Operational = Months_Operational+1;"
      DoCmd.RunSQL SQL
      
      SQL = "UPDATE tblAutoDate SET LastDate = Int(Now());"
      DoCmd.RunSQL SQL
   End If
   
End Function[/blue]

[blue]For testing, change the system time, then close/open the database.[/blue]

[purple]Thats it . . . . give it a whirl & let me know . . . .[/purple]

See Ya! . . . . . .

 

[TheAceMan1]

mama16 . . . . .

Almost forgot. [blue]The new table has to be initialized with a record.[/blue] So open the table and enter a date of [purple]1/1/1900[/purple]

Thats it . . . .

See Ya! . . . . . .

 

[thehorse]

AceMan,

I want to complement you on this post. Obviously from this and all your other posts, you have great command of ACCESS and SQL programming. Also, you take the time to develop codes to help people solve their problems. You, among the many other professionals on this forum, make this forum successful. Thanks for sharing your expertise.

thehorse

 

[TheAceMan1]

How are ya [blue]thehorse . . . . .[/blue]

[purple]Thanks![/purple]

[blue]To be sure, there are many others just as deserving, as there are others deserving more![/blue]

You take care! . . . . Ya Hear!

[purple]There is a way that seems right to a man, but in the end, it is Gods will that will prevail![/purple]

[purple]Proverbs 19:21[/purple]

See Ya! . . . . . .

 

[mama16]

Ace Man,
Thank You, Thank you, and Thank you. Ace man, you're wonderful. Keep it up!!!.
It worked!!! I tell you what, when I first ran it, it gave me an error but I figured that I need it to add a date to the table so it could work.
I agreed with everything the Horse said.

Again thanks a lot.

God bless you.