Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Budgeting and Control

Introduction

The local Charity Organization for Children allocates funds for disbursement to eligible individuals or entities under various categories.  The Accounts Section supervises the disbursement activities.  It is part of the responsibility of the Accounts Section to ensure that the total value of all payments of a particular category is not exceeding the allocated Amount.

We have been asked to write a program to monitor the payment activity and see that the Total Value of all payments stays within or up to the limit of the budgeted amount.  A sample screen, where payment details are recorded, is given below:

As you can see on the above screen; the Budget Amount of $10,000/- is allocated to the Poor Children's Education Fund.  This amount will be given away to eligible individuals or deserving institutions.  The amount is given away after due consideration of the merit of their issues.  The payment records are entered into the datasheet sub-form below.  Both forms are linked to the Category Code, which is an AutoNumber Field on the main Table. 

When a new record is entered into the sub-form with the payment value; the total value of all payment records, including the new record, is calculated and cross-checked with the budget amount on the main form.  If the total of all payment values is not less than or equal to the budget amount then an error message is displayed. The excess value entered is deducted automatically from the current payment value to adjust it and make the total of all payment values equal to the budget amount.

The focus is set back to the amount field so that the user can take appropriate action.

In the above example, the user is not prevented from making modifications to the Budget Amount.  But, this field can be locked immediately after creating a new main record with a value. If authorized changes to these records are required at a later period, then special access rights can be given to some category of authorized Users. But, this part involves Microsoft Access Security implementation. Leaving that part aside, we will take a closer look at the Datasheet Sub-form design and programs, where we have implemented the above procedure.

An image of the Payment Record Sub-Form Data Sheet Design View is given below:


A TextBox with Active Record not yet Saved Value.

We have created a Text Box at the Footer Section of the Form and written an expression to Sum() the Amount of all Payment records of the current Payment Category, except the current new record.  The new record value will not appear in the result of the Sum() Function till the record is saved in the Table. Even though the Text Box that we have created with the expression =Sum(Me![Amt]) in the  Footer Section of the Sub-Form, is not visible in the Datasheet view, we can refer to it in the Programs. For more tricks with Datasheet Form read the Article: Event Trapping and Summary on Datasheet

The current record value can be read directly from the form field (Me![Amt]) and add it to the result of the Sum() Function to get the Total Value of all records, including the record not yet saved to the Table.  This result can be checked with the Budget control value before the new record value is accepted in the current record.  If it is not acceptable, then the User is alerted so that corrective action can be initiated.

The Sub-Form Module Code.

The VBA Program Code written in the Sub-Form Module is given below:

Option Compare Database
Option Explicit
'Gobal declarations
Dim Disbursedtotal As Currency, BudgetAmount As Currency, BalanceAmt As Currency
Dim errFlag As Boolean, oldvalue As Currency

Private Sub Amt_GotFocus()
'Me!TAmt is Form Footer Total except the new record value
Disbursedtotal = Nz(Me!TAMT, 0)
BudgetAmount = Me.Parent!TotalAmount
oldvalue = Me![Amt]
End Sub

Private Sub Amt_LostFocus()
Dim current_amt As Currency, msg As String, button As Long

On Error GoTo Amt_LostFocus_Err
Me.Refresh
'add current record value to total and cross-check
'with main form amount, if the transactions exceed
'then trigger error and set the focus back to the
'field so that corrections can be done
current_amt = Disbursedtotal + Nz(Me!Amt, 0)
BalanceAmt = BudgetAmount - current_amt
errFlag = False
If BalanceAmt < 0 And oldvalue = 0 Then
    errFlag = True
    button = 1
        GoSub DisplayMsg
ElseIf oldvalue > 0 Then
    current_amt = (Disbursedtotal - oldvalue) + Nz(Me!Amt, 0)
    BalanceAmt = BudgetAmount - current_amt
    If BalanceAmt < 0 Then
        errFlag = True
        button = 1
          GoSub DisplayMsg
    End If
Else
    Me.Parent![Status] = 1
End If

Amt_LostFocus_Exit:
Exit Sub

DisplayMsg:
    msg = "Total Approved Amt.: " & BudgetAmount & vbCr & vbCr & "Payments Total: " & current_amt & vbCr & vbCr & "Payment Exceeds by : " & Abs(BalanceAmt)
    MsgBox msg, vbOKOnly, "Amt_LostFocus()"
Return


Amt_LostFocus_Err:
MsgBox Err.Description, , "Amt_LostFocus()"
Resume Amt_LostFocus_Exit
End Sub

Private Sub Form_Current()
Dim budget As Currency, payments As Currency

On Error Resume Next

budget = Me.Parent.TotalAmount.Value

payments = Nz(Me![TAMT], 0)

If payments = budget Then
 Me.AllowAdditions = False
Else
  Me.AllowAdditions = True
End If

End Sub

Private Sub Remarks_GotFocus()
If errFlag Then
  errFlag = False
  Me![Amt] = Me![Amt] + BalanceAmt
  BalanceAmt = 0
  Me.Parent![Status] = 2
  Me.Amt.SetFocus
End If

End Sub

Performing Validation Checks.

During data entry of the Payment Sub-Form, if the total of all payment values equals the Budget Amount then the Form will not allow adding more payment records. Exiting Payment records can be edited.

When any of the Budget Records on the Main Form becomes current it checks whether the total value of its payment records is equal to the Budget Value, if so the Payment Form is locked and will not allow adding more records. But, the existing payment records can be edited.

The following VBA Code on the Main Form Module keeps track of the above activity on the Main Form:

Main Form Module Code.

Option Compare Database

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub Form_Load()
DoCmd.Restore
End Sub

Private Sub Form_Current()
Dim budget As Currency, payments As Currency
Dim frm As Form
On Error Resume Next

Set frm = Me.Transactions.Form
budget = Me!TotalAmount
payments = Nz(frm![TAMT], 0)

If payments = budget Then
 frm.AllowAdditions = False
Else
  frm.AllowAdditions = True
End If

End Sub

Demo Database Download

Click the following link to download a Demonstration Database with the above Code.


Download Demo BudgetDemo.zip


Share:

5 comments:

  1. Thank you very much for sharing this. I have subscribed to your RSS feed. Please keep up the good work.

    ReplyDelete
  2. [...] Jean: Saving and Budgeting – Jean Chatzky blog :: The …LEARN MS-ACCESS TIPS AND TRICKS – Budgeting and ControlWhat Everybody Ought to Know About Personal Finance Budgeting Part [...]

    ReplyDelete
  3. Wow!, this was a real quality post. In theory I'd like to write like this too - taking time and real effort to make a good article... but what can I say... I keep putting it off and never seem to get something done

    ReplyDelete
  4. A thoughtful opinion and ideas I will use on my blog. You've obviously spent a lot of time on this. Congratulations!

    ReplyDelete
  5. Hi All, I have strange situation which I am struggling with. We have just performed a desktop refresh and at the same time delivered Windows 7 to the new machines. We use SCCM to deliver the machines and the build process works well. However, I have one niggling problem. The first time a user logs on to a freshly built machine the UAC kicks in and prompts for credentials on the Secure Desktop. In our case the UAC is stopping the application of a video card driver. The UAC and Secure Desktop are working as per design as we are making use of this feature. However, I am not expecting the UAC to kick in and stop the device driver installation. This is becuase I have expliclty defined the Video Card Device Setup Class "4d36e968-e325-11ce-bfc1-08002be10318" against the "Allow non-administrators to install drivers for these device setup classes" policy. The one thing I did note is that the certificate used by Microsoft to sign the driver expired on the 23/01/2010. I am not sure is this is the root cause?? I know it is the video card becuase if the Administrator credentials are entered the next event that is viewed is the "Drive finished installing" in the system tray. Can the UAC and this policy work in conjuntion? Has anybody else hit this issue?

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code