Help calling a function in module from a command button.

[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

 

[Guest]

If you want your code to execute only on the first working day of the month:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

If day(VBA.Date) = 1 Then
If FirstWorkDay(Date) = Date Then ' It is the first workday of the month
Call FirstDayRoutines
End If

Now, put all the things you want to do on that first day in a sub named
FirtDayRoutines. At least, that is the way I would do it. One thing to
consider - what if nobody opens the form this button is on during the first
work day of the month? This needs consideration, because if they don't, then
your FirstDayRoutines will not happen for that month.

 

[BFish via AccessMonster.com]

Thanks for the response Klatuu,

For the time being I substitued my query run for the Call FirstDayRoutines.
Now, I have three questions/issues.

1. My Holidays are in a form called frmComputedHolidays and control
Holidaydate which is actually calling upon another module which self
populates the form for any given year (Thank you Doug Steele!). Can I
reference this control instead of a table or query as is written in the
FirstWorkDayCode.

2. I have removed the Holiday code until everything else is working
correctly. When I run this on the 1st day of the month I get an error or
"Invailid procedure call or argument". Using a stop in the code at second
"Loop" statement no error, when I drop the stop down to "FirstWorkDay =
dtmBaseDate" is when the error will pop-up. By the way this code is
something I found from May of last year that you had referenced in another
post. Do you have any ideas on the error?

3. Would the "If day(VBA.Date) = 1 Then" need to be changed to "If day(VBA.
Date) < 5 Then" to account for 1st working day of the month being possibly up
to 4 days into the calendar or I am I over thinking this part.

Thanks again,

Bill Fischer

If you want your code to execute only on the first working day of the month:

Private Sub Command25_Click()
On Error GoTo Err_Command25_Click

If day(VBA.Date) = 1 Then
If FirstWorkDay(Date) = Date Then ' It is the first workday of the month
Call FirstDayRoutines
End If

Now, put all the things you want to do on that first day in a sub named
FirtDayRoutines. At least, that is the way I would do it. One thing to
consider - what if nobody opens the form this button is on during the first
work day of the month? This needs consideration, because if they don't, then
your FirstDayRoutines will not happen for that month.

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

[quoted text clipped - 49 lines]

Bill Fischer

 

[BFish via AccessMonster.com]

Update:

1. I could not find any way to reference the form control (still wonder if
it is possible), but is a moot point from other changes, see below.

2. I still found for some reason (date) was actually coming up as one day
behind system clock/calendar setting. Two strategically placed VBA.Date
solved this problem.

3. This would have been true if I had kept this code. If the 1st of the
month fell on a weekend the next work day obviously would have to be other
than the 1st of any given month.

And finally I took your first post Klatuu on system possibly not running the
functions if not used on the first work day (Dept. Manager out sick, power
outage ect., we defaults in place but nice to be overly protected than ???)
and decided to scrap first WorkDayRoutine. I went with an IF THEN Statement
to check Month & Year date when update query was last run with a date stamp
in table. This of course will only run the query if there is no date stamp
for current month, hence the first working day the system is in operation.
To fully guarentee the code to run this form defaults open on database open
with short cut on desk top, control box set to "no" and as a pop up. User
will have use the command button to exit and run the code.

Klatuu, Thank you for expertise and providing another view.

Bill Fischer

Thanks for the response Klatuu,

For the time being I substitued my query run for the Call FirstDayRoutines.
Now, I have three questions/issues.

1. My Holidays are in a form called frmComputedHolidays and control
Holidaydate which is actually calling upon another module which self
populates the form for any given year (Thank you Doug Steele!). Can I
reference this control instead of a table or query as is written in the
FirstWorkDayCode.

2. I have removed the Holiday code until everything else is working
correctly. When I run this on the 1st day of the month I get an error or
"Invailid procedure call or argument". Using a stop in the code at second
"Loop" statement no error, when I drop the stop down to "FirstWorkDay =
dtmBaseDate" is when the error will pop-up. By the way this code is
something I found from May of last year that you had referenced in another
post. Do you have any ideas on the error?

3. Would the "If day(VBA.Date) = 1 Then" need to be changed to "If day(VBA.
Date) < 5 Then" to account for 1st working day of the month being possibly up
to 4 days into the calendar or I am I over thinking this part.

Thanks again,

Bill Fischer

If you want your code to execute only on the first working day of the month:

[quoted text clipped - 17 lines]