Determining a new month, run procedure.

[Pis7ftw]

I've been stumped for the past couple of days trying to discern a method for a form to determine that it's a new month and run a procedure and only run it on month change.

My initial thought is:

Open recordset
move to the last record
compare the month in a date field within the last record to the current month
and run the procedure if the month is different.

I can't get this to work however and I'm not sure what I'm doing wrong. To be honest I typically fumble my way through VBA when it comes to opening recordsets. Here's what I got:

Dim rs As DAO.Recordset
Dim db as CurrentDB
Dim strSQL As String

Set rs = CurrentDB.OpenRecordset("FlightLog")
strSQL = SELECT [txtDate] FROM [FlightLog]

If rs.Recordcount <> 0 Then

rs.MoveLast

End If

If strSQL < Now() Then                'Or Me.txtSysDate which is a textbox on  
                                               ' the form with a system short date

Me.txtReqNumb = 0
Me.txtFltNumb = 0

End If

txtReqNumb and txtFltNumb are the values to be reset at the beginning of each month.

Am I on the right track and just have the VBA wrong? Is there an easier way to go about this?

Thanks for the help!

 

[pr2-eugin]

Is there an easier way to go about this?

There might be an easier way, but it depends on what you are trying to do.. I am unable to understand what is that you are trying to accomplish here.. BUT if it is just a matter of updating a few fields at the start of every month then I think it can be done by combination of an AutoExec Macro and UPDATE query..

But again this might get a bit messy, if the DB is not opened.. Or if the Start of the month falls on a non-working day.. More info might help us give you an appropriate solution..

 

[Pis7ftw]

I want to run a procedure at the beginning of each month. Automatically.

 

[gemma-the-husky]

if you do this somewhere on database startup

if day(date)=1 then
...etc
end if

however every user will get this, every time they log on. also if 1st is a sunday, say, and you do not open the dbs until the monday, the code doesn't executed.

another way is to have a scheduled task open the database (or another database), every day. check the date, and run the process if necessary. then close.

 

[Pis7ftw]

another way is to have a scheduled task open the database (or another database), every day. check the date, and run the process if necessary. then close.

Now that is something I didn't think about. My database utilizes a couple of vbScripts already. Shouldn't be a huge issue.