By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,788 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

balance record before move to new record

P: 2
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.
Feb 27 '17 #1
Share this Question
Share on Google+
2 Replies


PhilOfWalton
Expert 100+
P: 1,430
First of all, the ControlSource shoull hav an Nz function in case the data is blank

Expand|Select|Wrap|Line Numbers
  1. 1.debit...control source=Nz([entry Subform].[Form]![Text12])
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. 1.debit...control source=EntrySubform.Form!TxtDebit
  2.  
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.    If Balance <> 0 then
  4.         Msgbox "The balance is: " & Format(Balance, "Currency")
  5.         Cancel = True
  6.    End If
  7.  
Phil
Feb 28 '17 #2

P: 2
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.
Mar 1 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.