main form subroutine ignored by call from sub-form?

[qajaq]

I am creating a database system to track the financial transactions of a small, local non-profit. Because the agency's Treasurers are often elected more on the basis of their willingness than on their knowledge of bookkeeping or software, I am trying to include as much error-trapping as possible, but I am running into a problem whose source I do not understand.

One of the forms I've created is for recording deposits. Funds included in the deposit may come from any combination of six different sources, Funds from five of these sources can be entered in the aggregate. We do not need to know the individual details of the sales or fund-raising tickets, but only the sub-total for each of the five categories. The sixth category is contributions, and we do want to track these individually, so that our monthly reports can show and thank the individual contributors. There may be any number of individual contributions included in any given deposit.



So I've created two forms: f_dep_summ populates the table t_dep_summ ; and the sub-form f_dep_contrib popu lates the table t_dep_contrib.

The t_dep_summ table includes a primary key field (ID), as well as fields for the date, the deposit total, and five fields for the sub-totals of the various sales and fundraiser events revenues.

The t_dep_contrib table includes a foreign key (DepositID) field linked to the ID field in the t_dep_summ table, as well as fields for the contributors' names and amounts given.

The form and sub-form collect data and populate the tables without a problem. However, because I do not trust future Treasurers always to list accurately the amounts that make up the deposit total, I want to build in some error-checking. Specifically, I want the form to calculate sub-totals of the two categories of funds (i.e., one sub-total for combined sales and events revenues, and another sub-total for individual contributions), to add these two sub-totals together, and to compare this grand total with the amount entered as the deposit amount. If the numbers do not match, an error message is to be shown and the form not allowed to be closed until the error is corrected.

To help in developing this, I've created two fields in the main form, one displays the sub-total for the sales and fund-raiser events (sales_fr_subtotal) and the other displays the sub-total for individual contributions (contribs_subtotal). That works without flaw as well.

I've included a label field (lbl_reconciler) whose content changes to provide feedback to the user. Its initial value is “Enter a date and deposit amount.” After the deposit total is entered, the content of the label field is controlled through a VBA subroutine as follows:

Public Sub Reconcile()

    Dim sngDepTtl As Single ' Single-length number: Deposit Total
    Dim sngSlsFrs As Single ' Single-length number: Sales/Fundraisers sub-total
    Dim sngGrpCnt As Single ' Single-length number: Group Contributions sub-total
    Dim strRecMsg As String ' String: feedback to user


    ' deposit_total, sales_fr_subtotal, and contribs_subtotal are
    '   the names of fields on the main form.
    sngDepTtl = deposit_total.Value


    If Not IsNull(sales_fr_subtotal) Then
        sngSlsFrs = sales_fr_subtotal.Value
    Else
        sngSlsFrs = 0
    End If


    If Not IsNull(contribs_subtotal) Then
        sngGrpCnt = contribs_subtotal.Value
    Else
        sngGrpCnt = 0
    End If


    If sngDepTtl = sngSlsFrs + sngGrpCnt Then
        strRecMsg = "Deposit is in balance"
    Else
        strRecMsg = "Deposit is NOT balanced"
    End If
    lbl_reconciler.Caption = strRecMsg
End Sub

And in the Event tab of the Properties pane for each dollar-amount field, I call the Reconcile subroutine. For the fields in the main form, the VBA code is this:

Private Sub fieldname_AfterUpdate()

    Reconcile
End Sub

For the field in the sub-form, the VBA code is this:

Private Sub txt_rev_contrib_AfterUpdate

    Call Me.Parent.Reconcile
End Sub

The message in the lbl_reconciler field changes appropriately as soon as the Deposit Total is entered (reading “Deposit is NOT balanced”) and may change if the amounts in the Sales and Fundraisers section add up to the deposit total (when it will read “Deposit is in balance”). However, entries on the sub-form do not appear to affect the content of the lbl_reconciler field. If the Sales and Fundraisers add to less than the Deposit Total, then an amount equal to that difference is entered for a contributor, the field still displays “Deposit is NOT balanced.” And if the sum of the Sales and Fundraisers does equal the Deposit Total and a further amount is added as a contribution, the field still displays “Deposit is in balance.” These imbalanced amounts are entered into the data tables with no warning.

Why is my sub-routine, or at least the If-Then portion of it, being ignored?

[Bob Fitz]

It may help to put a breakpoint on the line:
If sngDepTtl = sngSlsFrs + sngGrpCnt Then
and look at the the values of each variable

[qajaq]

Thanks, Bob. I tried your suggestion.

When the sub-routine is called from data-fields on the main form, the values in the variables are just what I expect -- they mirror the values entered on the form (for those fields on the main form) and the "Else" value of zero for the field on the sub-form. All is cool.

However, when the subroutine is called from the data-field on the sub-form, all three variables return an empty line when I query their value. Interestingly, when I return to the main form and run it again, leaving the data in the sub-form's fields, I once again get the mirrored values in the variables, including the variable reflecting the sub-form's data field.

[Bob Fitz]

Not sure about this, but perhaps it would help to Requery or Recalc the main form or the controls in question before running your If/Then

[qajaq]

I don't know what you mean by that. I haven't written any queries. Is there a programmatic (VBA or Access) way to force recalculation? And do you mean the label control that's to be changed by the "If . . . Then" statement?

[Bob Fitz]

What I meant was, in the events where you make a call to your sub routine "Reconcile" try something like:
Me.Recalc
or
Me.Requery
or: on the main form you have 2 text boxes which display the totals from the sub forms. Lets call these "txtTotalOne" and "txtTotalTwo"
Me.[txtTotalOne].Requery
Me.[txtTotalTwo].Requery

[qajaq]

Okay, I tried that, but got nothing different. The sub-total fields and the variables derived from their values were already showing the correct numbers.

[Bob Fitz]

Can you post a copy of your db for me to play with when I get home from work today. Only need the forms in question and a few dummy records to show your problem

[qajaq]

Sorry I'm so tardy with this. Life has a way of getting in the way of all this fun! I just tried uploading my accdb file and learned that its size exceeds the forum's limit by a great deal. Instead, I've uploaded it to my website and I think you can locate and download it from there . . .

http://www.william-maloney.org/access

The file in question is NCFI-treasury.accdb. The form with which I'm having the difficulties is f_record_deposit. The embedded subform is sbf_group_contribs, and they populate the tables t_deposit_summary and t_group_contribs, respectively.

Thanks for your persistence.

[Bob Fitz]

Ok, I've downloaded it and will study it tomorrow.
As Arnie said "I will return!"

[Bob Fitz]

@qajaq

Please try the attached db.

[Missinglinq]

To be honest, I get lost trying to follow your explanation of what you're trying to accomplish, here; my fault, no doubt. You can reference a Control on one Form, from another Form, but the bottom line is that you cannot call an event in one Form from another Form, using, for example, your

Call Me.Parent.Reconcile

To be able to Call a procedure from multiple Forms it has to be defined as Public and has to reside in a stand-alone Module.

Also, procedures that return a value or values need to be defined as Functions, not Subs.

Linq ;0)>

[ssanfu]

Was going to have a look at the dB, but it has been removed