Ok, the basic formula should be correct. We need to make sure that we get current data
though when the pop-up is closed. In the OnClose event of the pop-up, let's calculate the
value and place it in the control on the subform.
To refer to a control on the subform, the syntax is:
Forms!NameOfMainForm!NameOfSubformControl.Form!NameOfControlOnSubform
The NameOfSubformControl is the name of the control on the main form that holds the
subform. To get this name, open the main form in design mode and open the Properties
sheet. Click on the subform ONE time. The properties sheet should show the name of the
control holding the subform. If you click more than once, you'll be in the subform and the
Properties sheet will show the name of the subform, not the control holding it.
In the OnClose event of the pop-up try something similar to:
Forms!frmMainForm!Subform.Form!strTotal_Unavailable =
DSum("[numNumber_Of_Days]", "tblDays_Unavailable", "[Id]=" &
Forms!frmMainForm!Subform.Form!Key)
In this case, strTotalUnabailable and Key are expected to be the names of the controls
(i.e. textboxes) not the names of the fields.
--
Wayne Morgan
Les said:
Wayne
Thanks for the formula unfortunately this does not solve the problem.
I should have said that the tblDays_Uavailable is not on the sub form. I
have a pop up form where the user can store details of the days unavailable.
This is where the tblUnavailable is linked to.
The idea is that the user puts the details on the popup (Datasheet) this
could be several rows over a period. This stores the reason for being
unavailable such as Holiday and a number showing how many days this is
"numNumber_Of_Days". I then need to display the total of these rows in the
subform. when the popup is closed.
The field [Id] on the popup is the linked to Field [Key] on the subform.
They are both number fields.
strTotal_Unavailable is just a text box on the sub form.
Hope this makes it clearer.
Les
Are all of the controls and fields on the subform? Is [Key] the name
of
the field and the
name of the control? If so, the duplicate name may be causing you a problem.
If all are on the subform try this for the Control Source of the strTotal_Unavailable
textbox.
=DSum("[numNumber_Of_Days]", "tblDays_Unavailble", "[Id]=" & [Key])
If Key is text instead of numeric
=DSum("[numNumber_Of_Days]", "tblDays_Unavailble", "[Id]='" & [Key] & "'")
You say you are wanting to put this in a "field" rather than in just a calculated text
box. Are you storing data that can be calculated (usually a bad idea)?
--
Wayne Morgan
On exit from a field in a sub form I need to put the result of a filtered
sum from a table in another field. I have tried to get DSum to work without
success.
On Exit from [strDateLeft] I need to sum the field
[numNumber_Of_Days]
in
table [tblDays_Unavailable] to field [strTotal_Uavailable] in the
sub
form.
This needs to be filtered with [tblDays_Unavailable] field [Id] to
match
the
sub form field [Key]
The path to my sub form is [frmAssociate_Details] [frmProject_Record]
