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:
Code:
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:
Code:
Private Sub fieldname_AfterUpdate()
Reconcile
End Sub
For the field in the sub-form, the VBA code is this:
Code:
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?