Subform/Calculations (Win 2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

I’ll answer q2, look at post 239080.
You could use post 238855 with modifcations.
Pat

Reply | Quote

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)

Reply | Quote

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.

• Open the form based on your table in design view.
• Select the control bound to DateReceived.
• Make sure that the Properties window is visible (View/Properties).
• Activate the Events tab of the Properties window.
• Click in the After Update event.
• Select Event Procedure from the dropdown list.
• Click the Builder button (the three dots to the right)
• 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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.)

Reply | Quote