B
BFish via AccessMonster.com
I am slowly learning programming but at this time I am in over my ablities.
I have a command button (Close) on a subform which on click I would like to
check for first work day in the month and run an update query if true
(eventually several queries). For this subform and eventualy also another
form I pulled a module for this event from the board, pasted it in as a
module object and is as follows:
Function FirstWorkDay(dtmBaseDate As Date) As Date
Dim intX As Integer
Dim blnFoundIt As Boolean
'Compliments of Dave Hargis
'Determines the first working day of the month for the date passed
'Set the date to the 1st of the month
dtmBaseDate = DateSerial(year(dtmBaseDate), month(dtmBaseDate), 1)
Do Until blnFoundIt
Do Until Weekday(dtmBaseDate, vbMonday) < 6
'If Saturday or Sunday, add a day
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
Loop
'If it is a weekday, see if it is a holiday
If IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmBaseDate & "#")) Then ' Not a holiday
blnFoundIt = True
Else
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
End If
Loop
FirstWorkDay = dtmBaseDate
End Function
Where I am having trouble is calling this function on click and how to pass
the current date for testing in the module. Here is the if code as it is now
for running on the 1st day of the month:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Me.Requery
If day(VBA.Date) = 1 Then
DoCmd.SetWarnings False
CurrentDb.Execute "qupdBusinessCode", dbFailOnError
DoCmd.SetWarnings True
End If
Can anyone help me configure the call for FirstWorkDay function? As all my
previous posts the people, information and willingness to help is outstanding.
Thank you each for your time.
Bill Fischer
I have a command button (Close) on a subform which on click I would like to
check for first work day in the month and run an update query if true
(eventually several queries). For this subform and eventualy also another
form I pulled a module for this event from the board, pasted it in as a
module object and is as follows:
Function FirstWorkDay(dtmBaseDate As Date) As Date
Dim intX As Integer
Dim blnFoundIt As Boolean
'Compliments of Dave Hargis
'Determines the first working day of the month for the date passed
'Set the date to the 1st of the month
dtmBaseDate = DateSerial(year(dtmBaseDate), month(dtmBaseDate), 1)
Do Until blnFoundIt
Do Until Weekday(dtmBaseDate, vbMonday) < 6
'If Saturday or Sunday, add a day
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
Loop
'If it is a weekday, see if it is a holiday
If IsNull(DLookup("[Holdate]", "Holidays", _
"[Holdate] = #" & dtmBaseDate & "#")) Then ' Not a holiday
blnFoundIt = True
Else
dtmBaseDate = DateAdd("d", 1, dtmBaseDate)
End If
Loop
FirstWorkDay = dtmBaseDate
End Function
Where I am having trouble is calling this function on click and how to pass
the current date for testing in the module. Here is the if code as it is now
for running on the 1st day of the month:
Private Sub Command25_Click()
On Error GoTo Err_Command25_Click
Me.Requery
If day(VBA.Date) = 1 Then
DoCmd.SetWarnings False
CurrentDb.Execute "qupdBusinessCode", dbFailOnError
DoCmd.SetWarnings True
End If
Can anyone help me configure the call for FirstWorkDay function? As all my
previous posts the people, information and willingness to help is outstanding.
Thank you each for your time.
Bill Fischer