One subform (SvcsProvided) records the individual services provided during this visit and sums the total hours.
The other subform (SvcMonthlyTotals) displays running totals for the given patient-volunteer combination over the entire month in which the current visit occurs. I have a button on the main form with vba to refresh this subform and it works fine. But I want to have the subform updated automatically as the user enters data. On the main form that already happens whenever I enter a patient, a volunteer, and a date. The issue is how to include the hours as they are added.
The problem is, I can’t figure out what control and what event to use to make that happen.
To briefly summarize the operation of SvcsProvided, for each service:
1. There’s a combobox where the service is selected;
2. There two textboxes to enter Time-In/Time-Out;
3. There’s a textbox to enter just the total Hours spent on the service;
4. There’s a locked textbox called “Duration” which holds the resulting service hours (converted to decimal).
That is, the user enters either Time-In/Time-Out or Service Hours, both options cancel the other, then the actual time is stored in Duration. The Durations are summed in an unbound textbox and that total is copied to the TotHours textbox on the main form. TotHours is bound to the primary table (tblServiceEvents) and that's what is aggregated by Pt-Vol and month. The point is, I'm counting the number of visits in the month, with the mileage for each visit, as well as the cumulative hours spent on multiple services.
The refresh code which works fine on the button is as follows:
Code: Select all
Private Sub cmdRefresh_Click()
Dim dHours As Double
dHours = Forms![frmServiceEvents]![sfrServicesProvided].Controls("SumHours")
With Forms![frmServiceEvents]
.SetFocus
.Controls("SvcMonth") = Format([SvcDate], "yymm")
.Controls("TotHours") = dHours
End With
Me.Refresh
End Sub