Calculating remaining spend in datasheet subform

[Ammo]

Hi,

I have created a main grants form called 'frm_GrantPot' that consists of a
sub form in datasheet view with several fields. The most valid fields in the
subform for this problem include:

AmountGrantAllocated, GrantSpentToDate, AllocatedGrantRemaining

What I wish to achieve is for the user to be able to key a figure under the
'AmountGrantAllocated' field and also be able to key a figure in the
'GrantSpentToDate' field without exceeding the corresponding
'AmountGrantAllocated' value keyed in originally for the record. I then would
like a figure to automatically appear under the 'AllocatedGrantRemaining'
field, which is the difference between the 'AmountGrantAllocated' and
'GrantSpentToDate' field values.

For example: AmountGrantAllocated (£1500), GrantSpentToDate (£200),
AllocatedGrantRemaining (£1300)

Hope somebody can help! Thank you!

Ammo

 

[Allen Browne]

Presumably you have a main table with information about the grant, and a
related table listing each of the items spent under the grant (one to many.)
Something like this:

Grant table:
GrantID AutoNumber primary key
GrantAmount Currency amout available to be spent.

GrantSpent table:
GrandSpendID AutoNumber primary key.
GrantID Number relates to Grant.GrantID
SpendDate Date/Time when this amount of the grant was spent.
SpendAmount Currency amount spent this time.
Descrip Text what the amount was spent on.

Use the subform's Before Update event procedure to check whether the amount
the user entered is available, like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim curSpent As Currency
Dim strMsg As String

If (Me.SpendAmount = Me.SpendAmount.OldValue) Or _
IsNull(Me.Parent!GrantAmount) Or IsNull(Me.SpendAmount) Then
'do nothing
Else
'Sum the *other* related records, and then add this one.
strWhere = "(GrantID = " & Me.GrantID & _
") AND (GrantSpendID <> " & Me.GrantSpendID & ")"
curSpent = Nz(DSum("SpendAmount", "GrantSpent", _
strWhere),0) + Me.SpendAmount
If curSpent > Me.Parent!GrantAmount Then
Cancel = True
strMsg = Format(Me.Parent!GrantAmount, "Currency") & _
" to spend, and this would be " & Format(curSpent,
"Currency")
MsgBox strMsg, vbExclamation, "Overspent"
End If
End If
End Sub


You might also like to offer the remaining balance as the default value when
the user enters a new record. Use the subform's Before Insert event
procedure:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim curSpent As Currency
Dim curBalance as Currency

With Me.Parent
If IsNull(!GrantAmount) Then
Cancel = True
MsgBox "Enter the total in the main form first."
Else
strWhere = "GrantID = " & !GrantID
curSpent = Nz(DSum("SpendAmount", "GrantSpent", strWhere),0)
curBalance = !GrantAmount - curSpent
If curBalance <= 0 Then
Cancel = True
MsgBox "Grant already spent."
Else
Me.SpendAmount = curBalance
End If
End If
End With
End Sub

 

[Ammo]

Hi Allen,

Thank you for your reply. I tried following your solution, but got stuck
because my form and sub form is laid out differently to how you presumed. The
form and sub form is made up of the following tables and applicable fields
for this problem:

tbl_GrantPot (Main Form)

GrantPotNumber: AutoNumber
GrantPotName: Text
AvailableGrant: Currency


tbl_GrantApplicant (Sub Form)

GrantApplicantNumber: AutoNumber
GrantPotNumber: (linking main form)
AmountGrantAllocated: Currency
GrantSpentToDate: Currency
AllocatedGrantRemaining: Currency


Hope this helps?

Best Wishes

Ammo

 

[Allen Browne]

In the subform's table, you are trying to store:
AmountGrantAllocated: the amount of this particular entry;
GrantSpentToDate: the sum of all entries for this grant;
AllocatedGrantRemaining: the difference between the AvailableGrant on
the main form, and GrantSpentToDate

Remove the last 2 from the subform's table. These are calculations, and you
are making a rod for your own back if you try to store them. One of the
basic rules of data normalization is not to store dependent data. You can
calculate these as needed using DSum() or totals queries, or whatever is
appropriate.

Once you have the normalized table structure, you will be able to use the
technique suggested in the previous post.