Inserting calculation field on form

[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)

For the 'TotalGrantRemaining' field on the main form I
wish to calculate the following for each grantpot record:

TotalGrantRemaining (on main form) = AvailableGrant (on
main form) - AmountGrantAllocated (for each record in
subform) where the 'GrantStatus' for each record in the
subform is = yes (ticked). I hope that makes sense, please
do not hesitate to ask more me for further clarification.
Cheers.

Kind Regards

AMMO

 

[Dennis]

In the control source of the TotalGrantRemaining field on main form enter this
=[AvailableGrant] -
DSum("[AmountGrantAllocated]","TableorQuery","[GrantPotNumber] = " &
Me.GrantPotNumber & " And [GrantStatus] = True")

'TableOrQuery' is the underlying source for your subform

 

[Ammo]

Hi Dennis,

When I copy the expression you created into
the 'TotalGrantRemaining' control source, I just get a
#Name? appear in the field in normal view. I tried
replacing the "TableorQuery" part of the expression with
the name of the table that the subform is based on, but
still not success. Do I need to do anything else with the
expressiob?

Regards

Ammo

-----Original Message-----
In the control source of the TotalGrantRemaining field on main form enter this
=[AvailableGrant] -
DSum

("[AmountGrantAllocated]","TableorQuery","[GrantPotNumber]
= " &

Me.GrantPotNumber & " And [GrantStatus] = True")

'TableOrQuery' is the underlying source for your subform

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)

For the 'TotalGrantRemaining' field on the main form I
wish to calculate the following for each grantpot record:

TotalGrantRemaining (on main form) = AvailableGrant (on
main form) - AmountGrantAllocated (for each record in
subform) where the 'GrantStatus' for each record in the
subform is = yes (ticked). I hope that makes sense, please
do not hesitate to ask more me for further clarification.
Cheers.

Kind Regards

AMMO

.

 

[SteveS]

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)

For the 'TotalGrantRemaining' field on the main form I
wish to calculate the following for each grantpot record:

TotalGrantRemaining (on main form) = AvailableGrant (on
main form) - AmountGrantAllocated (for each record in
subform) where the 'GrantStatus' for each record in the
subform is = yes (ticked). I hope that makes sense, please
do not hesitate to ask more me for further clarification.
Cheers.

Kind Regards

AMMO

Try this (on a copy of your database!):

Create a new QUERY.
Add the table "tbl_GrantApplicant", then drag down to the grid fields
"AmountGrantAllocated", "GrantPotNumber" and "GrantStatus".
In the Criteria row of GrantStatus enter True.
Make the query a totals query (click on the funny "E" in the toolbar).
In the Totals row for the field "AmountGrantAllocated", change "Group By" to
"Sum".
Save this query as "qryTotalAllocatedGrant" (without the quotes).

Create another new QUERY. Add table "tbl_GrantPot" and query
"qryTotalAllocatedGrant".
Join the two on "GrantPotNumber". Double click on the join line and select
join type 2.
Add the fields from "tbl_GrantPot" to the grid, then in the "Field" row of
an empty column paste:

GrantRemain: [AvailableGrant]-[SumOfAmountGrantAllocated]

Save this query as "qryGrantPot".

Create a new FORM.
Set the Record Source to "qryGrantPot".
Add the fields to the detail section and set the Default View to "Continuous
Forms".

If you want to see the amount of allocated grants, add
"SumOfAmountGrantAllocated" to the grid for the query "qryGrantPot", then to
the form.

 

[Ammo]

Hi Steve,

Thank you for your time to help me with this, but still no
joy I am afraid I basically want to be able to input
new records into the sub form (grant applicant details)
for each grant pot (main form), with
the 'TotalGrantRemaining' field on the main form being
calculated each time new record is added with Grant Status
= yes (ticked) Preferably I don't want to change my
current form design, so looking for some form coding to do
the job. Thanks for your time though. Anyone else have any
more suggestions?

Regards

Ammo

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

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)

For the 'TotalGrantRemaining' field on the main form I
wish to calculate the following for each grantpot record:

TotalGrantRemaining (on main form) = AvailableGrant (on
main form) - AmountGrantAllocated (for each record in
subform) where the 'GrantStatus' for each record in the
subform is = yes (ticked). I hope that makes sense, please
do not hesitate to ask more me for further clarification.
Cheers.

Kind Regards

AMMO

Try this (on a copy of your database!):

Create a new QUERY.
Add the table "tbl_GrantApplicant", then drag down to the grid fields
"AmountGrantAllocated", "GrantPotNumber" and "GrantStatus".
In the Criteria row of GrantStatus enter True.
Make the query a totals query (click on the funny "E" in the toolbar).
In the Totals row for the field "AmountGrantAllocated", change "Group By" to
"Sum".
Save this query as "qryTotalAllocatedGrant" (without the quotes).

Create another new QUERY. Add table "tbl_GrantPot" and query
"qryTotalAllocatedGrant".
Join the two on "GrantPotNumber". Double click on the join line and select
join type 2.
Add the fields from "tbl_GrantPot" to the grid, then in the "Field" row of
an empty column paste:

GrantRemain: [AvailableGrant]-[SumOfAmountGrantAllocated]

Save this query as "qryGrantPot".

Create a new FORM.
Set the Record Source to "qryGrantPot".
Add the fields to the detail section and set the Default View to "Continuous
Forms".

If you want to see the amount of allocated grants, add
"SumOfAmountGrantAllocated" to the grid for the query "qryGrantPot", then to
the form.

---
SteveS
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

.

 

[SteveS]

Hi Steve,

Thank you for your time to help me with this, but still no
joy I am afraid I basically want to be able to input
new records into the sub form (grant applicant details)
for each grant pot (main form), with
the 'TotalGrantRemaining' field on the main form being
calculated each time new record is added with Grant Status
= yes (ticked) Preferably I don't want to change my
current form design, so looking for some form coding to do
the job. Thanks for your time though. Anyone else have any
more suggestions?

Regards

Ammo

You don't have to change your form design. My example was just that - an
example of how to use a calculation in a query. Your form (the way I *almost*
always do it) should have a query as its Record Source.

If you want, delete most of your data (and ALL of the sensitive data),
compact it, zip it and sent it to me. I'm using A2K.

 

[Ammo]

Hi Steve,

I have emailed you the db as requested.

Kind Regards

Ammo

 

[SteveS]

Hi Steve,

I have emailed you the db as requested.

Kind Regards

Ammo

I received your database. After looking at it, I was still puzzled as to why
DSUM() wasn't working. So before I created the new queries, I took another
look at your formula for "TotalGrantRemaining".

There I was, looking at the form, thinking about creating the queries when
it happened!! Kind of like the guy that stayed up all night wondering where
the sun went when it went down, when it finally dawned on him! <g>

The DSUM() function is nothing more than a really, really limited query. You
have a source (the table), a field to search and a where clause (without the
where). And in a query, you *can't* use "Me.ControlName". You *have* to use
"Forms!FormName.ControlName".

Here is the new control source for "TotalGrantRemaining". (I rearranged the
terms)

=[AvailableGrant]-DSum("[AmountGrantAllocated]","tbl_GrantApplicant","[GrantStatus] = True and [GrantPotNumber] = " & Forms!frm_GrantPot.GrantPotNumber)


Then, to get the "TotalGrantRemaining" to update when you change the
subform, from the databasewindow, open "frm_GrantApplicant subform" in design
view. In the FORM AfterUpdate event, enter: Me.Parent.Refresh

It should look like this:

Private Sub Form_AfterUpdate()
Me.Parent.Refresh
' you might be able to to use instead
' Me.TotalGrantRemaining.Requery
End Sub

Two simple changes.... :-D It should work now......

 

[SteveS]

Oops...

That should have been:

' Me.TotalGrantRemaining.Refresh


.Requery will reset the current record to the top of the recordset...not
good...

 

[Ammo]

Hi Steve,

Thank you for all your help and especially the time you
spent on this. Yes, was going to mention about it should
be. refresh instead of .requery. Can't believe it was the
smallest of things as well, suppose that is the way of the
world, its always the little things. Anyways thanks again.

Best Wishes

Amarjeet