Project Spend = Total Approved - Total Spent

[Ammo]

Hi,

I have main form called 'frm_GrantPot' that has the
field 'AvailableGrant', the user can enter a figure in
this field.

This main consists of a sub form
called: 'frm_GrantAllocation' subform which allows the
user to enter multiple records (datasheet view) which
relates to the record in the main form. Each record that
the user enters in the sub form, the user enters a figure
in a field called 'AmountGrantAllocated'

I wish to create a field on the main form
called 'GrantSpentToDate'

The formula will be

GrantSpentToDate (Main Form) = AvailableGrant (Main Form)-
AmountGrantAllocated (Sub Form)

How do I implement this?

Hope someone can help?

Best Wishes

Ammo

 

[John Smith]

Create a text box in the footer of the subform called TotalAllocated, and set
it's ControlSource to

=Sum(AmountGrantAllocated)

Set Visible to false so that it cannot be seen. On the main form the
ControlSource for GrantSpentToDate will be

=AvailableGrant-frm_GrantAllocation!TotalAllocated

 

[Ammo]

Hi John,

Thank you for your help, I have done what you suggested,
however I keep getting 'Name?' in the 'GrantSpentToDate'
textbox, do you know why this is happening?

Ammo

 

[John Smith]

Sorry, my fault, I left a bit out of the Control Source:

=AvailableGrant-frm_GrantAllocation.Form!TotalAllocated

This assumes that frm_GrantAllocation is the name of the sub-form control,
which may or may not be the same as the name of the form that it contains.

If you still have any problems it would be worth making TotalAllocated visible
temporarily, just to check that it is showing the correct value. One other
thing that you might need, if there is any possibility of a Null
AmountGrantAllocated, change it's Control Source to:

=Sum(Nz(AmountGrantAllocated, 0))

Likewise, if AvailableGrant could ever be Null put an Nz() round that in
GrantSpentToDate.

 

[Ammo]

Sorry John,

Still no luck, I still get the 'Name?' in
the 'TotalGrantRemaining' textbox, even when I build the
formula from the expression builder. Here is the
expression I use:

=[AvailableGrant]-[Forms]![frm_GrantAllocation Subform]!
[TotalGrantAllocated]

 

[John Smith]

Close, but you have the wrong reference to Forms, it is not the forms
collection that you need but the Form property of the subform control:

=[AvailableGrant]-[frm_GrantAllocation Subform].[Form]![TotalGrantAllocated]

Access is picky isn't it?

--
HTH
John

Still no luck, I still get the 'Name?' in
the 'TotalGrantRemaining' textbox, even when I build the
formula from the expression builder. Here is the
expression I use:
=[AvailableGrant]-[Forms]![frm_GrantAllocation Subform]!
[TotalGrantAllocated]

-----Original Message-----
Sorry, my fault, I left a bit out of the Control Source:

=AvailableGrant-frm_GrantAllocation.Form!TotalAllocated

This assumes that frm_GrantAllocation is the name of the sub-form control,
which may or may not be the same as the name of the form that it contains.
If you still have any problems it would be worth making TotalAllocated visible
temporarily, just to check that it is showing the correct value. One other
thing that you might need, if there is any possibility of

a Null AmountGrantAllocated, change it's Control Source to:

=Sum(Nz(AmountGrantAllocated, 0))
Likewise, if AvailableGrant could ever be Null put an Nz

() round that in GrantSpentToDate.

 

[Ammo]

Cheers John, it worked Thanks for your time.

Best Wishes

Amarjeet

-----Original Message-----
Close, but you have the wrong reference to Forms, it is not the forms
collection that you need but the Form property of the subform control:

=[AvailableGrant]-[frm_GrantAllocation Subform].[Form]! [TotalGrantAllocated]

Access is picky isn't it?

--
HTH
John

Still no luck, I still get the 'Name?' in
the 'TotalGrantRemaining' textbox, even when I build the
formula from the expression builder. Here is the
expression I use:
=[AvailableGrant]-[Forms]![frm_GrantAllocation Subform]!
[TotalGrantAllocated]

-----Original Message-----
Sorry, my fault, I left a bit out of the Control Source:

=AvailableGrant-frm_GrantAllocation.Form!TotalAllocated

This assumes that frm_GrantAllocation is the name of

the
sub-form control,

which may or may not be the same as the name of the

form
that it contains.

If you still have any problems it would be worth making TotalAllocated visible
temporarily, just to check that it is showing the

correct
value. One other

thing that you might need, if there is any possibility

of
a Null AmountGrantAllocated, change it's Control Source to:

=Sum(Nz(AmountGrantAllocated, 0))
Likewise, if AvailableGrant could ever be Null put an

Nz
() round that in GrantSpentToDate.

Thank you for your help, I have done what you suggested,
however I keep getting 'Name?' in the 'GrantSpentToDate'
textbox, do you know why this is happening?

-----Original Message-----
Create a text box in the footer of the subform called
TotalAllocated, and set
it's ControlSource to
=Sum(AmountGrantAllocated)
Set Visible to false so that it cannot be seen. On the
main form the
ControlSource for GrantSpentToDate will be
=AvailableGrant-frm_GrantAllocation!TotalAllocated

.