Inserting calculation field on form based on records

[Ammo]

Hi All,

I have created a form called 'frm_GrantPot'
with 'tbl_GrantPot' as the record source.

The form consists of the following fields:

GrantPotNumber, GrantPotName, AvailableGrant,
TotalGrantRemaining (calculated field with the following
expression; =[AvailableGrant]-[frm_GrantApplicant
subform].Form!AmountGrantAllocated

The main form also consists of a subform
with 'tbl_GrantApplicant' as the record source and
consists of the following fields:

GrantApplicantNumber, GrantStatus (yes/no data type),
DateGrantAllocated, GrantRecipient, ReasonGrantAllocated,
AmountGrantAllocated, GrantSpendProgress, GrantConditions,
GrantPotNumber (Foreign Key)

I wish to calculate the following for
the 'TotalGrantRemaining' field for the total records
keyed into the subform:

TotalGrantRemaining = AvailableGrant -
AmountGrantAllocated where GrantStatus = yes (ticked)

Hope someone can help me as I am not a full time Access
Programmer and wish I could spend more time investigating
this problem. Thank You.

Kind Regards

AMMO

 

[Ammo]

Can anyone help, or have I posted this in the wrong
category.

 

[Tim Ferguson]

I wish to calculate the following for
the 'TotalGrantRemaining' field for the total records
keyed into the subform:

TotalGrantRemaining = AvailableGrant -
AmountGrantAllocated where GrantStatus = yes (ticked)

This is a guess, but something like

me!AvailableGrant - DSum( _
"AmountGrantAllocated", "tbl_grantapplicant", _
"GrantPotNumber = " & Me!GrantPotNumber)

you might have to call something likeMe!txtTotalGrantRemaining.Requery if
the DSum is not automatically updated frequenly enough -- try an event
like the AfterUpdate for the AmountGrantAllocated control on the subform
form.

Hope that helps


Tim F

 

[Ammo]

Thanks Fred for your help, but it still does not work, I
am reposting this problem in the 'Forms Coding' category.
Thanks again.

Ammo

-----Original Message-----



This is a guess, but something like

me!AvailableGrant - DSum( _
"AmountGrantAllocated", "tbl_grantapplicant", _
"GrantPotNumber = " & Me!GrantPotNumber)

you might have to call something likeMe!

txtTotalGrantRemaining.Requery if

 

[Guest]

Sorry mean't tim

-----Original Message-----

Thanks Fred for your help, but it still does not work, I
am reposting this problem in the 'Forms Coding' category.
Thanks again.

Ammo

txtTotalGrantRemaining.Requery if

 

[Tim Ferguson]

Sorry mean't tim

S'okay -- I've been called worse in my life...


Tim F