Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subform/Calculations (Win 2000)

    Greetings,
    1. I have a main form called Household and a subform called Applicants. In the main form I have NumOfApp field, where I would like to automatically be filled depending on how many people applied in the Applicants form.
    2. Also, I have DateReceived and DateDue fields in the Applicants form.
    The DateDue is calculated by adding 10 workdays(DateReceived + 9) to the
    DateReceived and put the result into DateDue field automatically.
    So the user don

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform/Calculations (Win 2000)

    1. You can put a text box on the main form and set its Control Source property to

    =[Applicants].[Form].[RecordsetClone].[RecordCount]

    Replace Applicants by the name of the subform as a control on the main form; this is not necessarily the same as the name of the subform in the database window.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Subform/Calculations (Win 2000)

    I'll answer q2, look at <post#=239080>post 239080</post#>.
    You could use <post#=238855>post 238855</post#> with modifcations.
    Pat

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform/Calculations (Win 2000)

    2. What is the question? How to add a number of workdays to a given date? Here are two functions you can use for this. These functions don't take holidays into account. There are several recent threads about holidays; if you do a search for holiday, you'll find them. Added: Patt has posted some links in the meantime.

    ' The NextWorkday function returns the first workday (not Saturday or Sunday) after aDate.

    Function NextWorkday(aDate As Date) As Date
    Dim d As Date
    d = aDate + 1
    Do While WeekDay(d) = vbSunday Or WeekDay(d) = vbSaturday
    d = d + 1
    Loop
    NextWorkday = d
    End Function

    ' The WorkdayAdd function returns the date aNumber workdays (not Saturday or Sunday) after aDate.
    ' Example: WorkdayAdd(#10/18/02#, 4) returns #10/24/02#.

    Function WorkdayAdd(aDate As Date, aNumber As Long) As Date
    Dim d As Date
    Dim i As Long
    d = aDate
    For i = 1 To aNumber
    d = NextWorkday(d)
    Next i
    WorkdayAdd = d
    End Function

    You can use this function to calculate DateDue:

    =WorkdayAdd([DateReceived],9)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Subform/Calculations (Win 2000)

    More about 2:

    You need both functions, because the second (WorkdayAdd) uses the first (NextWorkday). You should copy both functions into a new module in the database. Save the module as - for example - basDateFunctions.

    One possibility is *not* to have a field DateDue in the table at all, but to make it a calculated field in a query based on the table. That way, DateDue will always be up-to-date. The expression for DateDue in the query would be

    DateDue:WorkdayAdd([DateReceived],9)

    You can use this on a form the same way you can use a field in the table. The major difference is that a calculated field is not editable by the user.

    If you need DateDue to be a field in the table, you must update it whenever DateReceived is updated. You can only do this on a form, not in the table itself.
    <UL><LI>Open the form based on your table in design view.
    <LI>Select the control bound to DateReceived.
    <LI>Make sure that the Properties window is visible (View/Properties).
    <LI>Activate the Events tab of the Properties window.
    <LI>Click in the After Update event.
    <LI>Select Event Procedure from the dropdown list.
    <LI>Click the Builder button (the three dots to the right)
    <LI>You get an empty even procedure. Complete it so that it looks as follows:

    Private Sub DateReceived_AfterUpdate()
    DateDue = WorkdayAdd(Me.DateReceived,9)
    End Sub

    If necessary, replace DateReceived and DateDue by the actual names of the controls on your form bound to DateReceived and DateDue.[/list]HTH

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkdayCalculations (Acc 2000)

    Thanks for your reply Hans,
    The workdayAdd part works fine. A similar question though, I have two combo boxes (AppStatus with drop down NEW, REDETERM etc & EligStatus with drop down ELIG, DENIED etc) depending on what the user selects from each field, EligDays = the difference between two fields. For example, if NEW from AppStatus & ELIG from EligStatus selected then EligDays = [AppRec] - [EligDate]. How do I get the result (in work day) automatically to be filled in EligDays field?

    Thanks,
    DD

  7. #7
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    East Coast(USA)
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: WorkdayCalculations (Acc 2000)

    Hello Hans,
    Thanks for your post.
    This is the code I

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: WorkdayCalculations (Acc 2000)

    Private Sub UpdateEligDays()
    Dim dteApp As Date
    Dom dteElig As Date

    Select Case AppStatus
    Case "New"
    dteApp = AppRec
    Case "Redeterm"
    ...
    End Select

    Select Case EligStatus
    Case "Elig"
    dteElig = EligDate
    Case "Denied"
    ...
    End Select

    EligDays = dteApp - dteElig
    End Sub

    If you want EligDays to be updated each time the user selects a value from the AppStatus combo and from the EligStatus combo, call this from the AfterUpdate event of both combo boxes. It it's OK to update only when the user leaves the record, call it from the BeforeUpdate event of the form.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: WorkdayCalculations (Acc 2000)

    The situation is more complicated than I had guessed from your earlier post (I thought that the combo boxes acted independently of each other in determining which dates to subtract). Although the code could be simplified to some extent, I'd say leave it like it is, with one exception: I would use EligDays = Null instead of EligDays = "" in the last Else clause. Null creates a truly empty value.

    (And I would remove the comments, because they don't say more than the code already does, so they only make the code look cluttered. But that's just a private opinion.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •