How Do I Link a Textbox on a Form To a Function?

[JessiRight77]

Hello... I need to calculate the number of workdays, and found some
useful code by Arvin Meyer.

I have placed two fields on my form (StartDate and EndDate), along with
an unbound textbox (txtCountDays) where I want to display the number of
workdays. I have also followed Mr. Meyer's instructions to create a
tblHolidays.

My question... and it's so basic that I'm almost embarrassed to ask...
How do I link the textbox to the function code??? I went into the
properties of the textbox, clicked the "Event" tab, and selected "Event
Procedure" in the AfterUpdate event. Then I clicked the Build button
and pasted the function code there (within the Private Sub
txtCountDays_AfterUpdate() and End Sub lines).

It doesn't work, however... nothing is displayed in the textbox after I
enter two dates, so I must be doing something wrong. Does anyone know
where I'm messing up?

Thanks,
Jessi

The code that I pasted in its entirety is as follows:
______________________________

Option Compare Database

Private Sub txtCountDays_AfterUpdate()

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between them
' Note that this function has been modified to account for holidays. It
requires a
' table named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

End Sub

 

[strive4peace]

Hi Jessi,

the function probably goes into a general module (but you didn't say
what it was, so I can't look). Then, to assign the function, type this
in an event property on the property sheet:

=FunctionName()

where any arguments would go into the parentheses


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

Hello... I need to calculate the number of workdays, and found some
useful code by Arvin Meyer.

I have placed two fields on my form (StartDate and EndDate), along with
an unbound textbox (txtCountDays) where I want to display the number of
workdays. I have also followed Mr. Meyer's instructions to create a
tblHolidays.

My question... and it's so basic that I'm almost embarrassed to ask...
How do I link the textbox to the function code??? I went into the
properties of the textbox, clicked the "Event" tab, and selected "Event
Procedure" in the AfterUpdate event. Then I clicked the Build button
and pasted the function code there (within the Private Sub
txtCountDays_AfterUpdate() and End Sub lines).

It doesn't work, however... nothing is displayed in the textbox after I
enter two dates, so I must be doing something wrong. Does anyone know
where I'm messing up?

Thanks,
Jessi

The code that I pasted in its entirety is as follows:
______________________________

Option Compare Database

Private Sub txtCountDays_AfterUpdate()

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
between them
' Note that this function has been modified to account for holidays. It
requires a
' table named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

End Sub

 

[JessiRight77]

Thank you for your reply, Crystal, but I'm still not sure what to do
with this. I did the following:

I don't have a "module"... should I create one? If so, should I give
it the same name as the function (WorkingDays2)? When I click on the
"Code" icon, I see "General" in the top drop-down box, and
"WorkingDays2" in the drop-down box right beside it. Am I in the
right place?

I then edited the code to remove the first and last lines of:
Private Sub txtCountDays_AfterUpdate()
End Sub

And I then deleted the "Event Procedure" in the AfterUpdate Event;

And I typed the name of the function into the "Control Source" of the
textbox:
=WorkingDays2()

Now, the textbox displays an error message of "#Name?"

Thanks for your help,
Jessi

 

[strive4peace]

Hi Jessi,

you're welcome

firstly, delete the code in the module behind your form that doesn't
work -- Private Sub txtCountDays_AfterUpdate() and the WorkingDays2 function

to create a general module sheet:

click on the module tab in the database window (as opposed to showing
tables, queries, forms, report,,,)

when you switch to the modules, perhaps nothing will be showing.

click on the NEW button to make a new general module

this is where you probably put the code for WorkingDays2

compile the code
(from the menu --> Debug, Compile ... and fix it until nothing happens
when you compile)

save the code

go back to the Access Database (leave VBA window open)

now, go to the design view of your form

most likely, WorkingDays2 returns a value given two dates... this can be
used as the ControlSource of a calculated field...

make a textbox control

change the ControlSource -->
=WorkingDays2(parameters)

If this does not get it, please provide a link to the WorkingDays2 code
so we can see what it does


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hello Crystal! ... Fancy meeting you here! ... I recently joined here ...
pretty neat ... just thought I'd visit since I didn't have a cool bike to go
riding ... AND there were no "viscosity checks" requested!

 

[strive4peace]

Hi Brent,

Nice to see you!

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*

 

[JessiRight77]

Thanks so much, Crystal! I really appreciate it... it worked
beautifully.

Jessi

 

[strive4peace]

you're welcome, Jessi happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote programming and Training
strive4peace2006 at yahoo.com
*