Setting Default Values in Subform Based on Other Controls

[kjohns]

I have a database for capturing OR utilization. On my main form (frmBlockEntry), I list the surgeons' OR block time (including fields StartTime and EndTime). On the subform (frmReleaseEntry), I chunks of the original block that were used, released, or given up to another surgeon, so I'll have one or more entries (including fields StartTime and EndTime in each one).

As a way of trying to simplify data entry, I was trying to code the following:
- If it's the first subform entry for a given block time, set the StartTime of the subform equal to the StartTime for the main form.
- On subsequent subform entries for the same block time, set the StartTime equal to the EndTime in the previous subform entry until the EndTime of the subform matches the EndTime in the main form.

Here's what I have so far on my subform events:
Private Sub EndTime_AfterUpdate()
Me![EndTime].Tag = Me![EndTime].Value
End Sub

Private Sub Form_Current()
If Me.CurrentRecord = 1 Then
Me.StartTime.DefaultValue = Forms!frmBlockEntry!StartTime.Value
Else
Me.StartTime.DefaultValue = Me.EndTime.Tag
End If
End Sub

This seems to work pretty well, but when I move to a new frmBlockEntry record, I get a "Run-time error '94: Invalid use of null" error, which points to the line in purple above. I'm guessing that it's because I've navigated to the new form and haven't yet entered the StartTime on frmBlockEntry (main form).

Should I be moving the code to a different Event? Is there a way to clear out/reset the EndTime.Tag? And can anyone figure out how to not have it set a default value for a start time once the subform's EndTime equals the main forms EndTime.

Thanks in advance for help on part or all of this issue!

 

[vapid2323]

Can you use a nested if statment to also check if Forms!frmBlockEntry!StartTime.Value is null?

Something like

If Nz(Forms!frmBlockEntry!StartTime.Value, 0) = 0 Then End '(or what ever action you need)

 

[kjohns]

Thanks, vapid2323! That took care of the Run-time error. I'm now able to move to a new record in my subform without that error.

In case anyone cares, my updated code for my subform is as follows:
Private Sub EndTime_AfterUpdate()
Me![EndTime].Tag = Me![EndTime].Value
End Sub
Private Sub Form_Current()
If Nz(Forms!frmBlockEntry!StartTime.Value, 0) Then
If Me.CurrentRecord = 1 Then
Me.StartTime.DefaultValue = Forms!frmBlockEntry!StartTime.Value
Else
Me.StartTime.DefaultValue = Me.EndTime.Tag
End If
End If
End Sub

If anyone has any feedback on getting it to stop entering a default value once the subform's EndTime equals the main forms EndTime, I'd appreciate it. It's more of a cosmetic issue, as I don't really like seeing an extra line in my subform with a StartDate, even it it's not really saved as an entry.