balance record before move to new record

[daoud]

hello
I'm useing access 2007
I have 3 unbound textbox in form
1.debit...control source=[entry Subform].[Form]![Text12]
2.credit...control source=[entry Subform].[Form]![Text14]
3.balance...control source
this 3 unbound textbox take data form unbound textbox in subform as Text12:sum[debit] .. Text14:sum[credit]
balance:=[Text12]-[Text14]
i make new unbound textbox for balance in form
the control source is. =[entry Subform].[Form]![Text16]
he give me balance zero when debit = credit
but my question is how can use validation rule or before update or any simple way to check if this balance not equal to be zero before move to new record in form.
how to get msg balance not equal.

[PhilOfWalton]

First of all, the ControlSource shoull hav an Nz function in case the data is blank

1.debit...control source=Nz([entry Subform].[Form]![Text12])
 

Secondly, you will make life much easier for yourself if you give the text boxes meaningful names.

Thirdly, I strongly advise against having spaces in form names or field names so we are now down to

1.debit...control source=EntrySubform.Form!TxtDebit
 

Now to get to your question (at long last)

You need to check the balance before updating your form, so do your calculation for the balance as you indicate and then on the Before Update, try the following code

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
   If Balance <> 0 then
        Msgbox "The balance is: " & Format(Balance, "Currency")
        Cancel = True
   End If
 

Phil

[daoud]

PhilofWalton thank you so much to help me.
I'll take your advices to me as a teacher, I'm not expert in ''MS Access'' , my knowledge come by self learning ,I'm accountant.
I'll try your code.

Thanks again.