Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by JessiRight77@yahoo.com, Jul 28, 2006.

  1. JessiRight77@yahoo.com

    JessiRight77@yahoo.com
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Eh Janam Tumhare Lekhe... Official Movie Link... Announcements Jun 3, 2015
    Sikhism Complete Shalok Mehala 9 (Link) Videos Apr 22, 2014
    UK British PM orders probe into Thatcher link to Operation Bluestar (UPDATED) Breaking News Jan 14, 2014
    India Gwalior: A Unique Link to India's History Breaking News Nov 16, 2013
    Researchers establish link between racism and stupidity General Sep 28, 2013

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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
    *



    JessiRight77@yahoo.com wrote:
    > 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
    >
     
  4. JessiRight77@yahoo.com

    JessiRight77@yahoo.com
    Expand Collapse
    Guest

    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
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    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
    *



    JessiRight77@yahoo.com wrote:
    > 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
    >
     
  6. datAdrenaline

    datAdrenaline
    Expand Collapse
    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! :)

    --
    Regards,
    Brent Spaulding
    datAdrenaline


    "strive4peace" wrote:

    > 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
    > *
    >
    >
    >
    > JessiRight77@yahoo.com wrote:
    > > 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
    > >

    >
     
  7. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Brent,

    Nice to see you!

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



    datAdrenaline wrote:
    > 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! :)
    >
     
  8. JessiRight77@yahoo.com

    JessiRight77@yahoo.com
    Expand Collapse
    Guest

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

    Jessi
     
  9. strive4peace

    strive4peace
    Expand Collapse
    Guest

    you're welcome, Jessi ;) happy to help

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



    JessiRight77@yahoo.com wrote:
    > Thanks so much, Crystal! :) I really appreciate it... it worked
    > beautifully.
    >
    > Jessi
    >
     

Share This Page