combine two vba codes in Access Forms

[Grega]

Hi,
I don’t know much about VBA coding but I would be very grateful if someone would help me in writing a code for my database Access file.
I already have a code that control value in General ledger. So whenever balance is not 0 that means that filed »Diff1« is not equal to 0, there is a MSg Box »Debit and Credit Must be equal«, as showed in example below (see STEP 2).
The problem is that this works only when I close the main Form. However, I want to make the same conditions when I add new record to a main and not when I exit form?
So that would be something like this:

STEP 1: This code would be for MAIN form named "EntryHead"
*****************************************************
Private Sub Form_Current()
If Me.NewRecord Then
do the STEP 2 ???
*****************************************************

STEP 2: This code already works in my SUB form named "GeneralLedger_subform"
*****************************************************
Private Sub GeneralLedger_subform_Exit(Cancel As Integer)
If Me.Diff1.Value < 0 Or Me.Diff1.Value > 0 Then
MsgBox "Debit and Credit Must be equal", vbOKOnly
Diff1.SetFocus
Else
MsgBox "Records are Saved", vbOKOnly
End If
End Sub

*****************************************************

Can I somehow merge SPEP1 and STEP 2 in one code?



thanks for your help!
Greg

[OBP]

Greg, It is not clear if the code works on a single record or over a group of records.
If it is a new record in the Main form there won't be any entries in the subform so Diff1 will be zero.
After creating the new record in the mainform you could pass focus to the subform and then to a field on the sub form so that your code is initiated when the subform is exited.

[Grega]

Hello OBP, thank you for your reply.
Yes, it is a new record in a main form and the Diff1 is not zero. Here is one example: Invoice amount of 1000 is posted with cost of 800 and VAT of 100, so 100 remains unbalanced. In this case and if user want to add new record I want access to show msg box "Debit and Credit Must be equal". This works when user Exit but I want this msg already when user want to add new record (new Invoice) and Diff is not eqal to zero.

[OBP]

So the Diff1 field is on the mainform?
It could be duplicated on the subform.
So does the new mainform record have to balance before another new mainform record is created?

[Grega]

Yes Diff1 is on mainform and yes the new mainform have to be balanced before another record of mainform is created.
"It could be duplicated on the subform". - I don’t know what do you exactly mean by that. One Document in a mainform have (normally) at least two records on the subform (at least one Debit and one Credit). Document is linked with mainform.

[OBP]

You can sum the fields of records in a continuous subform, so the balance can be shown on the subform.
To do what you want I would remove the Navigation buttons and add a VBA "New Record" Command Button instead.
Then when someone tries to create a new record you can control it.

[Grega]

Thank you for advice, I did put a New Record button.
I am not sure how to control it, could you write a code like shows STEP 2 above for the mainform? Thank you!

[OBP]

The first thing is does the button have VBA code or does it use a macro?
I am not sure how that code works on the subform when Diff1 is on the Mainform, because to refer to it should me.parent.Diff1, which was why I asked the question about where it was.
Have you tried just pasting your code in and add an Exit Sub after the "Diff1.SetFocus" ie before it actually goes to a new record.

ps your code should have error trapping in it to identify problems for you.

[Grega]

Thank you OBP!! I've just added "Exit Sub" as you said, and YES that work now!!!, great:yes.