Believe it or not I find it fun to figure things like this out. This should
work for you. I don't know exactly how you want to handle the printing of the
message itself - I can't tell for sure if you always just want to print one
message, and that message varies depending on the time. If that's the case,
then you should have a textbox somewhere in the report and in the SELECT CASE
statement, you would just set the value of the that field, e.g.
Me.Controls("txtMessage").value = "Every 4 mos message" (or whichever)
I'm not sure how your report is set up and where you would want the message
displayed. If, for example, the text box will be in the report header, then
you would create a Report Header OnFormat event procedure and put the SELECT
CASE statement there. I think you can put the function in the report module -
just paste it after the OnFOrmat event procedure you create.
You'll need to create a table that would keep track of the initial date the
message is to be printed and the next date that it should be printed. If you
already have a table you use to keep track of application parameters and it
just has one row in it, you can define those fields in that table, otherwise
create a new table with those two fields. I've called the two fields
startPrintDate and nextPrintDate. They should be defined as Date/Time fields.
So for example, if you want the initial date to be considered 1/1/2009,
meaning that's where the every-1-year and every-3-years is calculated from,
set startPrintDate to 1/1/2009, and set the nextPrintDate field to 5/1/2009,
since that is 4 months after the initial one. If the every-1-years and
every-3-years started at 3/1/2008, then you would set startPrintDate to that
date and set nextPrintDate to 7/1/2009 since that would be the next 4 monht
interval that would be coming up. You shouldn't have to do anything more with
that table once you have those fields defined and those initial values set -
the report code will handle it after that.
Again, put the SELECT CASE in the event procedure you create for whatever
report section the message text box will be in:
Select Case GetMessageType
Case -1
MsgBox "whatever you want to do in case of an error"
Case 1
Me.Controls("txtMessage").value = "Every 1 yr message"
Case 3
Me.Controls("txtMessage").value = "Every 3 yrs message"
Case 4
Me.Controls("txtMessage").value = "Every 4 mos message"
End Select
End Sub
The put this function after the event procedure you created:
Public Function GetMessageType() As Integer
On Error GoTo HandleError
Dim curYear As Integer
Dim curMonth As Integer
Dim startYear As Integer
Dim startmonth As Integer
Dim nextPrintDate As Date
Dim startPrintDate As Date
Dim newPrintDate As Date
nextPrintDate = DLookup("nextPrintDate", "tblName")
startPrintDate = DLookup("startPrintDate", "tblName")
If Date >= nextPrintDate Then
curYear = Year(nextPrintDate)
curMonth = Month(nextPrintDate)
startYear = Year(startPrintDate)
startmonth = Month(startPrintDate)
If curMonth = startmonth Then
If (curYear - startYear) Mod 3 = 0 Then
GetMessageType = 3
Else
GetMessageType = 1
End If
Else
GetMessageType = 4
End If
newPrintDate = DateAdd("m", 4, nextPrintDate)
DoCmd.RunSQL "UPDATE tblName Set nextPrintDate = #" & newPrintDate & "#"
Else
GetMessageType = 0
End If
Exit Function
HandleError:
'do your error handling here
GetMessageType = -1
End Function
You'll need to change 'tblName' in the DLookups and in the DoCmd.RunSQL to
whateer the table name is that has the two date fields in it. Hope you can
get itto work without too much hassle.
