Increment by one

[mama16]

Ok, I got the Update Query to increment my fields by one. Now, how can I do it so Access does it automatically at the beginning of each month. Otherwise, I'll have to run that query every month. Is there a way, and how?
Thank you

 

[BobStubbs]

You need these objects to achieve this:

-- A control table, in which (apart from anything else you might want to hold) you have a LastUpdateMonth field.

-- An autoexec process which runs every time the database is opened by a user with data update rights

-- VBA code to store the current month number in the control table

-- VBA code to compare the current month number, with the number stored in the control table.

Here is some example code which may help:

1. Update the control table with the current month number

sub SetLastUpdateMonth
    dim strSQL as string
    dim strThisMonth as string

    strThisMonth = format$(date(),"mm")

    strSQL = "INSERT INTO tblControl (LastUpdateMonth) "
    strSQL = strSQL & "VALUES ('" & strThisMonth & "')"

    DoCmd.SetWarnings (False)
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings (True)
End Sub

2. Get the stored month number from the table and compare with the current month:

Public Function CheckMonth()
    dim strThisMonth as string
    dim strStoredMonth as string

    strThisMonth = format$(date(),"mm")
    strStoredMonth = DLookup"[LastUpdateMonth]", "tblControl")
    
    if strStoredMonth <> strThisMonth then
        Docmd.RunQuery qryYourUpdateQuery
        SetLastUpdateMonth
    end if

end function

To set up an Autoexec macro:

-- Open the main database window
-- Click on [Macros]
-- Click the [New] button
-- In the 'Action' column, choose 'RunCode'
-- In the 'Function Name' field which appears in the lower half of the window, choose or type the name of the visual basic function, e.g. CheckMonth()

Close the Macros window and save changes.

The process will 'remember' to run your update query, even if the database is not opened until a few days into the next month. I think it would also be sensible to log the fact that the query has run, so you can check that it is working OK.

I hope that these ideas are helpful.


Bob Stubbs

 

[mama16]

Thanks, but I'm a little confuse

This is what the table looks like

State Months_In_operation.

FL 1
TX 15
WA 24
KS 23
NC 42
SD 32

I want Access to increment those numbers every time a new month comes. The above code would do it?

 

[BobStubbs]

Start by creating an Update Query which will do this job when you run it manually. Just list the [Months_In_Operation] field in the query. The 'update to' line in the query should say:

[Months_In_Operation] + 1

Run this query, and check that it does add '1' to every record's Months_In_Operation field.

Save the query - let's call it qryAddOneMonth

Now look back at my example code. Put your query name in the DoCmd.RunQuery line:

    if strStoredMonth <> strThisMonth then
        Docmd.RunQuery qryAddOneMonth
        SetLastUpdateMonth
    end if

I hope this makes more sense now. All the rest of my code is just designed to decide when to run the update query, following these business rules:

-- Run the query automatically when required
-- Run the query the first time the database is opened in a new month, whatever the date
-- Never run the query more than once in a month

That's what takes all the rest of the code! You have to give Access:

-- Somewhere to 'remember' what the month was when the query was last run
-- A way to check the remembered month against the current month
-- A way to run the query when needed, then update the remembered month





Bob Stubbs