Continuous form with Conditional Check

[ianward]

Hi all,

I am looking for a way to have a check on a continuous form, I have a subform which allows me to select a number of areas (localities) to which a service is provided to, I have been asked to add in an apportionment split field which i have done - no problem there. But what i would like to do is check the sum of the continuous form = 100% -
eg: Pipe delimited

Hyndburn | 60%
Burnley | 25 %
Ribble Valley | 10%

Total: 95%

I have in the form footer a text box which adds up for me, but i would like to incorporate a msgbox highlighting that the apportionment has not been entered correctly. I have tried the following on afterupdate on both the percent field and total field:

If Me.txt_ApportionCheck <> 1 Or Me.txt_ApportionCheck <> 0 Then
MsgBox "Apportionment Entered Does NOT Equal 100%", , "Calculation Error"
End If

But the code seems to fire before the total column has updated.

Can anyone please point me in the right direction?

Many Thanks - Ian

 

[Kafrin]

You're right that the calculated control is not updated until after the code runs. One alternative is to add up the recordsetclone values, ie:

Private Sub PercentCtrlName_AfterUpdate()
    Dim Tot As Single
 
    DoCmd.RunCommand acCmdSaveRecord
    Tot=0
 
    With Me.RecordsetClone
        .MoveFirst
        Do Until .EOF
            Tot = Tot + PercentCtrlName
            .MoveNext
        Loop
    End With
 
    If Tot <> 1 Or Tot <> 0 Then
        MsgBox "Apportionment Entered Does NOT Equal 100%", , "Calculation Error"
    End If
 
End Sub

 

[ianward]

Thanks for the response kafrin,

I have just found another way using the onexit event for the subform

Private Sub sfrm_ServiceLocality_Exit(Cancel As Integer)
If DSum("PercentageApportionment", "tbl_ServiceLocality", "serviceID=[serviceID]") <> 1 Then
MsgBox "Apportionment Error, Please check values", , "Calculation Error"
End If
End Sub

This does seem to suit better as the msgbox will not trigger until the user attempts to move away from the subform.