Consulting

Results 1 to 9 of 9

Thread: combine two vba codes in Access Forms

  1. #1
    VBAX Regular
    Joined
    Jun 2020
    Posts
    15
    Location

    combine two vba codes in Access Forms

    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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Jun 2020
    Posts
    15
    Location
    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.
    Attached Images Attached Images

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  5. #5
    VBAX Regular
    Joined
    Jun 2020
    Posts
    15
    Location
    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.

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  7. #7
    VBAX Regular
    Joined
    Jun 2020
    Posts
    15
    Location
    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!

  8. #8
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  9. #9
    VBAX Regular
    Joined
    Jun 2020
    Posts
    15
    Location
    Thank you OBP!! I've just added "Exit Sub" as you said, and YES that work now!!!, great.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •