Where to place code to automatically refresh a subform

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Where to place code to automatically refresh a subform

Post by Jeff H »

I have 2 subforms on a main form. The main form records the date of one visit along with the patient-volunteer combination. The mileage for this visit is also entered there.

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
Is this enough information for you to suggest somewhere I could use the refresh code to automatically update the MonthlyTotals subform?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

When exactly do you want the second subform to be updated? Immediately when the user enters Time-In and Time-Out or Service Hours? Or when the user moves to a different record in the first subform? Or ...?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

When the Duration control has been populated. A complete record in that subform includes Service and Duration. At that point the second subform should reflect the change in aggregated time.

In fact, there will usually only be one record in that subform.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

How and when do you populate the Duration control?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

That happens here in the After-Update events for SvcHours, Time-In, and Time_out

Code: Select all

Private Sub SvcHours_AfterUpdate()

Me.TimeIn = Null
Me.TimeOut = Null
Me.Duration = Nz(Me.SvcHours * 24)
Me.Refresh

End Sub


Private Sub TimeIn_AfterUpdate()

If Not IsNull(Me.TimeOut) Then
    Me.SvcHours = Null
    Me.Duration = DateDiff("n", Me.TimeIn, Me.TimeOut) / 60
End If

End Sub


Private Sub TimeOut_AfterUpdate()

If Not IsNull(Me.TimeIn) Then
    Me.SvcHours = Null
    Me.Duration = DateDiff("n", Me.TimeIn, Me.TimeOut) / 60
End If

End Sub
This seemed to me like an action spot where I could put the refresh code, but I couldn't get it to work.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

Thanks. And how does SumHours get populated? Is it a calculated control, or do you use code?
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

Calculated: "=Sum([Duration])"

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

I'd add code to the end of the After Update event procedures of SvcHours, Time_In and Time_Out:

Code: Select all

    With Me.Parent ' that is the main form as seen from the subform
        .Controls("TotHours") = Me.Controls("SumHours")
        .Refresh
    End With
You'll have to test whether this has undesirable side effects.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

Great. I'll try that and test it out. I'll let you know.

Thanks!

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

I'm sorry to say it doesn't seem to be working. Maybe I've added the code incorrectly. Here's what I've got now.

Code: Select all

Private Sub SvcHours_AfterUpdate()
Dim dHours As Double

Me.TimeIn = Null
Me.TimeOut = Null

Me.Duration = Nz(Me.SvcHours * 24)
Me.Refresh

With Me.Parent
    .Controls("TotHours") = Me.Controls("SumHours")
    .Refresh
End With

End Sub


Private Sub TimeIn_AfterUpdate()

If Not IsNull(Me.TimeOut) Then
    
    Me.SvcHours = Null
    Me.Duration = DateDiff("n", Me.TimeIn, Me.TimeOut) / 60
    
End If

With Me.Parent
    .Controls("TotHours") = Me.Controls("SumHours")
    .Refresh
End With

End Sub


Private Sub TimeOut_AfterUpdate()

If Not IsNull(Me.TimeIn) Then
    
    Me.SvcHours = Null
    Me.Duration = DateDiff("n", Me.TimeIn, Me.TimeOut) / 60
    
End If

With Me.Parent
    .Controls("TotHours") = Me.Controls("SumHours")
    .Refresh
End With

End Sub
I’ve gone through several times to document what happens:

1. Enter the event data (Pt, Vol, Date) and the Totals subform displays the correct data (i.e. the cumulative previously entered data for this month).

2. Enter Miles, then move to Services subform and select a Service.

3. Enter time in any of the 3 time entry controls and press tab once:
  • The Totals subform updates to show this new visit and the miles entered on the main form;
  • The SumHours control (Services subform) shows the Duration;
  • The TotHours control (Main form) does not;
  • The Totals subform does not reflect the new hours; and
  • Focus does not move to the next control.
4. Press tab again to enter another Service:
  • After entering time for the new Service, SumHours updates to show the cumulative hours;
  • TotHours and the Totals subform update the hours, but only for the previous Service that was entered (i.e. not the cumulative hours for both Services).
  • The same pattern continues when adding a third Service.
However, the Refresh button on the Main form does still work. I tried to see if I could trigger that from the Services form but I still get confused with the referential syntax. I tried Form_frmServiceEvents.cmdRefresh_Click and Form_frmServiceEvents (“cmdRefresh_Click”).

The logic of the operation is this: A complete record includes a Patient, a Volunteer, a Date, Mileage (could be 0), and at least one Service with Time. Each time a new Service is entered, the query underlying the Totals subform should recalculate the aggregated Count of Visits, Sum of Hours, and Sum of Miles.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

I'm afraid I'd have to see (a copy of) the database, so that I can see what's happening.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

Yes sir. I'm ready for you this time. I made a copy and blanked all the identifying information.

Unfortunately ... I thought I was ready, but my zip file is 629kb and I'm being told it's too big.

I still have your email address. May I send it to you?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

Yes, that's fine.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

It's in the mail...

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

Thanks, I just received it. I'll be away for a couple of hours; I will look at it when I return.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

I've sent you an email about an error message that I got while entering data in the main form.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Where to place code to automatically refresh a subform

Post by HansV »

Hi Jeff,

I've been looking at it. I'm afraid it isn't going to work the way you want. SumHours is the sum of the hours in the records as stored; you cannot update is while a record in the subform is being edited, and if you try to save the record in the subform after each edit, chaos ensues.
So you'll have to use the After Update event of the subform:

Code: Select all

Private Sub Form_AfterUpdate()
    ' Make sure that SumHours is up-to-date
    Me.Recalc
    ' Update TotHours on the main form
    Me.Parent!TotHours = Me.SumHours
    ' Update the other subform
    Me.Parent!sfrSvcMonthTotals.Requery
End Sub
I'll send you the database with the revised subform by email.
Best wishes,
Hans

Jeff H
4StarLounger
Posts: 415
Joined: 31 Oct 2017, 20:07

Re: Where to place code to automatically refresh a subform

Post by Jeff H »

I got the db and, actually it works fine! As I explained in my email, and repeat for any onlookers watching this thread, I just needed one additional line to your code to make it work. Once the underlying table is updated the requery is able to include the new hours each time a new Service is entered. Perfect!

Thank you so much!!

Code: Select all

Private Sub Form_AfterUpdate()
    ' Make sure that SumHours is up-to-date
    Me.Recalc
    ' Update TotHours on the main form
    Me.Parent!TotHours = Me.SumHours
    
    'Update the underlying table
    Me.Parent.Refresh
    
    ' Update the other subform
    Me.Parent!sfrSvcMonthTotals.Requery
End Sub