Disable navigation if subform total <> main form total?

[Phil5673]

I have seen something similiar in another thread that had no resolution. Here goes...

I have a check amount on the main form. The subform is the distribution of the check amount. The sum of the distribution amounts must be equal to the main form check amount. If it is not, I would like to prevent the user from leaving the record. Any suggestions?

 

[grantwilliams]

If your sub-form only contains one record (that is each distribution amount is not a separate record), then you might be able to try something like.

Private Sub Form_BeforeUpdate(Cancel as Integer)

    Dim DistTotal

    DistTotal = Forms!Main!Sub.form!Field1.Value + Forms!Main!Sub.form!Field2.Value etc......

    If Me!CheckTotal.Value <> DistTotal Then

        MsgBox "The distribution amounts don't match the check total."
        Cancel = True
        Exit Sub

    End If

End Sub

If the distribution amounts are separate records, you could include in the query for your main form a total of the distributed amounts and assign this to a hidden text field. THEN, when you insert a new distribution record to the subform, use the AfterUpdate of the subform to requery the main form.

You'd then use the same code as above except instead of adding up the distributions, you compare the check total to the amount in the hidden text field. If they don't match, Cancel = True and Exit.

Probably not the best way of doing it, but I hope this helps none the less.

Grant

 

[Phil5673]

Okay. Here is where I am at. There can be multiple records in the distribution amounts. I have a control on the form footer of the distribution subform which the sum of the distribution amounts. In the Before Update event code, I am checking the deposit amount against the sum of the distribution amounts.

In entering a new record, and moving from the main form to the subform which does not have records entered yet, the Before Update event of the main form triggers and displays the message. How do I work around this?

Phil

 

[Phil5673]

Also, the Before Update event does not trigger when I press the add new record button (the default record navigation provided with access forms).

Phil

 

[grantwilliams]

One long way around this would be to create custom record selector buttons on your form and disable the standard record selectors.

This would be a long way around the problem, but at least you could introduce validation into your code. e.g.

Private Sub Form_NextRecord_Click()

    If Me!CheckValue = Me!Sub.Form!Total Then
        DoCmd.GoToRecord , , acNext
    Else
        MsgBox "The totals don't match"
        Exit Sub
    End If

End Sub

As I said, it's a long way around the problem - you'd need similar code for each of your buttons. I'll keep looking into a better way of doing it and get back to you.

 

[TheAceMan1]

How are ya Phil5673 . . . . .

Have a look at the [blue]NewRecord[/blue] property in VBE help.

See Ya! . . . . . .