Validating a Financial Journal entry

Question

I sorry to trouble you again kind people, but I trust I will be assisted again , I have a financial Journal entry below created in the sub form called  frmFinancial (Child) linked to the main form (Parent)Called FrmFJournal. Now I’m required to write a validation procedure which will ensure that the following is achieved:

1. If total Sum Debits and Total Sum Credits are not equal then , data shall not be saved
2. If Sum Debits and  Sum Credits not equal then decision is either correction is required or Journal cancelled

SubForm
Details	Account Code	Debit	Credit
Travel expenses	52000100	£25.00
Cash Book	63000120		£25.00
Tea & Coffee	45000133	£10.00
Petty Cash	64000121		£5.00
Cash Book	63000120		£5.00
Total		£35.00	£35.00

Answer 1

Thank you so much to all who came to my rescue , your efforts is highly valued and I mean it . The challenge I have with the Code below  is that it does not still stop access from saving un balanced journal:

If Me.TotalDebits <> Me.TotalCredits then

    MsgBox "Credits and Debits do not match, please correct!"

    Me.subformcontrol.SetFocus

End if

The second suggestion of using un bound form and code  Nz(Credit,0)-Nz(Debit,0).   , I still have a challenge because the account CODE must be looked up by users otherwise there will be a lot of miss posts again and time consuming. I have also researched other existing normal accounting software they work as follows:

(1) If the debits and credits are not equal then the systems rejects the entire transaction until correction is effected

(2) If the debits and credits are balanced then the journal is accepted and saved temporally until a senior official with authority authorise it , that is when it is moved to the final table and saved permanently

But for me since this is just an assignment I'm only interested on :

 (1) If the debits and credits are not equal then the systems rejects the entire transaction until correction is effected

The second point may be it is to complicated for now I will leave it.

Once again thank you so much Ken & Scott, any further help in the issues above will be highly appreciated I'm very sure the above points right now are there in most of the accounting packages either solved or un attended to.

Regards

Chris

Answer 2

1. If total Sum Debits and Total Sum Creditsare not equal then , data shall not be saved

As there will always be times during data entry when the rows don't balance, the only way you could do this with a bound form would be to bind the form to a daybook table, posting the rows from the daybook to a ledger table only when they balance, i.e. HAVING SUM(Debit) = SUM(Credit).  The daybook table's entries can be temporary, emptied by means of a DELETE statement being executed once the rows have been posted to the ledger by means of an INSERT INTO statement.

The alternative would be to use an unbound form and post the rows to the ledger by means of a set of INSERT INTO statements within a Transaction, only committing the transaction when the rows balance. The balance following the insertion of each row could be held in a variable declared as Currency simply by incrementing the value of the variable with Nz(Credit,0)-Nz(Debit,0).  The value of the variable would need to be zero for the rows to balance and the Transaction committed.

Answer 3

Here's part of your problem. Assuming you are using Bound forms, the data is saved to the table as soon as a record loses focus. So you have to either make the corrections or delete the records created.

Why not just do this all in the subform? Since you have the totals, Use the Lost Focus event of the subform control:

If Me.TotalDebits <> Me.TotalCredits then

    MsgBox "Credits and Debits do not match, please correct!"

    Me.subformcontrol.SetFocus

End if

You could also have a delete button Whihc deletes all the entries for that Journal entry ID.

Answer 4

Here is the way I'm calculating the debits & credits:

(1) In the sub form , I go to the sub form footerand sum the debits and credits , two controls are created , that is the Totaldebits & Totalcredits.

(2) To bring it to the main form , again I go to the main form footer and create two controls called FinalDebits & FinalCredits , and then I use an expression builder, I simply reference the totaldebits & totalcredits in the subform. then the same totals appear on the main form.

The reasons why I have to go round like that is because if I simply sum debits & credits from the main form I will receive the following error messages:

(1) #error

(2) #Name?

Now where do I attach the suggested code? Example:

(1) in Main form ????????

(2) or Sub Form?????

Thank you so much for a quick response , I really value your effort.

Regards

Chris

Answer 5

How are you currently calculating the Total Debit and Total Credit? If you're doing this with builtin Access methods, then you could just compare the two values directly in the Form's BeforeUpdate event:

Sub Form_BeforeUpdate(Cancel As Integer)

  If Me.TotalDebit <> Me.TotalCredit Then

    MsgBox "Debits do not match Credits"

    Cancel = True

  End If

End Sub

If you're using some other method then you'd have to let us know.