Auto Notification

[David McLean]

Hi Everyone,

I'm trying to figure out a way to have a form pop up in my database
automatically on the first day of each month that would show a query. would
anybody be able to point me in the right direction?

some details
Query Name: qryLast3Months
Form Name: frmAutoNotify

The query is embedded as a subform on the form, with a few other boxes that
I required. Really, I'm jusdt looking for some direction on how to make it
open automatically on the first of the month.

Take Care,

 

[Dirk Goldgar]

in message

Hi Everyone,

I'm trying to figure out a way to have a form pop up in my database
automatically on the first day of each month that would show a query.
would
anybody be able to point me in the right direction?

some details
Query Name: qryLast3Months
Form Name: frmAutoNotify

The query is embedded as a subform on the form, with a few other boxes
that I required. Really, I'm jusdt looking for some direction on how to
make it open automatically on the first of the month.

Note that in order for this to happen, the database must be opened on the
first of the month. If the database isn't opened, nothing is going to
happen.

Do you currently have a startup form specified for your database? If you
do, you can easily have code in the form's Open event procedure to check
whether this is the first day of the month, and open the desired form if it
is. For example,

'----- start of example code -----
Private Sub Form_Open(Cancel As Integer)

If Day(Date) = 1 Then
DoCmd.OpenForm "frmAutoNotify"
End If

End Sub
'----- end of example code -----

If you don't have a startup form, you could either create one for the sole
purpose of making this test, or you could use an autoexec macro to make the
test and open the form (or to run a procedure that would do the same thing).

 

[David McLean]

Thanks for the quick response. It hadnt occured to me it might not be opened
on the first of the month every month (with weekends and holidays and such).
Would it be much more difficult to specify the first Monday of each month?

Thank you very much.
Dave

 

[Jeff Boyce]

Pardon my intrusion ...

Rather than try to specific an absolute day (i.e., the 1st, or the first
Monday, or ...), why not test to see if the last time this routine ran was
in the current month? If not, run it. If so, skip it.

You'd need to store the date the routine last ran, for comparison purposes.

I've used a variation on this approach to see if anyone (multi-user) has
already logged into the application on this day. If no one has, the person
logging in (first) triggers the routine, otherwise, since the routine
already ran (and a record with the day's date written), the routine doesn't.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dirk Goldgar]

Thanks for the quick response. It hadnt occured to me it might not be
opened on the first of the month every month (with weekends and holidays
and such). Would it be much more difficult to specify the first Monday of
each month?

Opening on the first Monday is easy enough:

'----- start of code -----
Dim dtFirstMonday As Date

dtFirstMonday = DateSerial(Year(Date), Month(Date), 1)
dtFirstMonday = dtFirstMonday - Weekday(dtFirstMonday, vbMonday)

If Date = dtFirstMonday Then
DoCmd.OpenForm "frmAutoNotify"
End If

'----- end of code -----

However, holidays present a more complicated picture. Usually this is
handled by maintaining a table of holidays, which you may or may not choose
to load into an array or recordset for frequent use. The code here:

http://www.mvps.org/access/datetime/date0012.htm
Date/Time: Doing WorkDay Math in VBA

.... gives a whole set of functions for handling work days.

That said, you still have the possible problem of someone just not opening
the database on the first working day of the month. If you need to allow
for that, the solution is also simpler: record in a table the date
frmAutoNotify was last opened. In your startup code, look up that date and
see if it's in the same month as the current date. If it isn't, then open
frmAutoNotify and record the current date in the table.

 

[David McLean]

thanks for the help. I manage to get it to store the last opened date in a
table (tblLastOpened), and am playing with the code to get frmAutoNotify to
open properly on the first date the database is opened when the month is not
the same (if I understood you correctly, the date that is currently stored
in the table is 25 Jun 09, and if I were to not open the database again
until 4 Jul 09, it would open frmAutoNotify. After that, it wouldn't open
that form automatically until the first day in August (or any subsequent
month) that I opened the database).

I'm not even sure where to begin with the coding for this, as my vba skills
are novice at best. You've helped quite a bit already...if you wouldnt mind
a bit more, or even a link to a website that could point me in the right
direction, I'd very much appreciate it. I'm trying to ge this coding to run
in the OnLoad for frmSwitchboard.

thanks.
Dave

 

[Dirk Goldgar]

thanks for the help. I manage to get it to store the last opened date in a
table (tblLastOpened), and am playing with the code to get frmAutoNotify
to open properly on the first date the database is opened when the month
is not the same (if I understood you correctly, the date that is currently
stored in the table is 25 Jun 09, and if I were to not open the database
again until 4 Jul 09, it would open frmAutoNotify. After that, it wouldn't
open that form automatically until the first day in August (or any
subsequent month) that I opened the database).

I'm not even sure where to begin with the coding for this, as my vba
skills are novice at best. You've helped quite a bit already...if you
wouldnt mind a bit more, or even a link to a website that could point me
in the right direction, I'd very much appreciate it. I'm trying to ge this
coding to run in the OnLoad for frmSwitchboard.

It would be something like this:

'----- start of code -----
Private Sub Form_Load()

Dim dtLastOpened As Date

' Get the date on which frmAutoNotify was last opened.
' If it has never been opened, pick an arbitrary date a long,
' long, time ago.

dtLastOpened = _
Nz(DLookup("LastOpenedDate", "tblLastOpened"), #1/1/100#)

' Check to see if a month boundary has passed since the
' last-opened date.

If DateDiff("m", dtLastOpened, Date) > 0 Then
DoCmd.OpenForm "frmAutoNotify"
End If

End Sub
'----- end of code -----

Note that the DateDiff function looks for date-interval *boundaries*, not
whole intervals. So if the form was last opened on #6/30/2009#, then it
would be opened again on #7/1/2009#, because DateDiff("m", #6/30/2009#,
#7/1/2009#) = 1.

 

[David McLean]

Thank you very much sir. It worked perfectly.

It would be something like this:

'----- start of code -----
Private Sub Form_Load()

Dim dtLastOpened As Date

' Get the date on which frmAutoNotify was last opened.
' If it has never been opened, pick an arbitrary date a long,
' long, time ago.

dtLastOpened = _
Nz(DLookup("LastOpenedDate", "tblLastOpened"), #1/1/100#)

' Check to see if a month boundary has passed since the
' last-opened date.

If DateDiff("m", dtLastOpened, Date) > 0 Then
DoCmd.OpenForm "frmAutoNotify"
End If

End Sub
'----- end of code -----

Note that the DateDiff function looks for date-interval *boundaries*, not
whole intervals. So if the form was last opened on #6/30/2009#, then it
would be opened again on #7/1/2009#, because DateDiff("m", #6/30/2009#,
#7/1/2009#) = 1.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)