MS Access Master Detail Form getting error in calculated control

[hayat]

I am new in MS Access. I created two simple tables ExpMast and ExpDet which are linked thru PK/FK (1:M) relationship. In order to create a form, I selected the Table ExpMast and then clicked Create->Form which automatically selected all fields from ExpMast and displayed all fields of ExpDet in a block (named Child12 by it) where I can enter multiple records. Now, I believe the block is NOT a subform (which of course I want to avoid as a beginner). At the bottom of the form, I want to display total of all amounts (AMOUNT being the field in ExpDet and the column displayed in the block). I created a control text box and in the Control Source property, typed in "=sum(Amount)" but it is displaying "#Error". I also tried "=sum(child12.amount)" but the same error.

[ADezii]

@hayat

=DSum("[Amount]", "ExpDet", "[<Foreign Key>] = " & Forms!ExpMast![ID])

[hayat]

ADezii,
Thank you. It worked. However, when I enter a new record (in the master block) and a few related records in the child block, (OR update any amount in the child block,) the calculated total doesn't show up (or not updated) until I go back to previous records and come back. What I want to do is whenever I enter into the calculated field, the updated total should show up.
Once again thanks for the help.

Hayat

[ADezii]

@hayat
In the AfterUpdate() Events of both the Master and Child Form, you can force an Update of the Calculated control, namely:

Private Sub Form_AfterUpdate()
  Forms![ExpMast]![<Calculated Field Name>] = _
  DSum("[Amount]", "ExpDet", "[<Foreign Key>] = " & Forms!ExpMast![ID])
End Sub

[hayat]

I am sorry, it didn't work. Just to remind, it's just one form (called EXPENSE) having two blocks. I tried the code which is also shown here but the calculated field is not updated.

Private Sub Form_AfterUpdate()
[Forms]![Expense]![CalcTotal] = _
DSum("[Amount]", "ExpDet", "[ExpID] = " & [Forms]![Expense]![ID])
End Sub

I also tried this code in CHILD12 "on exit" event, CalcTotal "on enter" and "Got Focus" events but nothing worked.

[ADezii]

@hayat
Would you be able to Attach a copy of the Database to a Message?

[hayat]

Please find attached the DB. Thanks.

[ADezii]

It appears as though you will need a rather unique solution to this problem. Here is one that will work:

1. Remove the Control Source from the [CalcTotal] Text Box on the Expense Form.
2. Set the Timer Interval Property of the Expense Form to 1000 (1 sec.). You can play with this value if you like, but remember it is in milliseconds.
3. Copy and Paste the following code to the Timer() Event of the Expense Form:
   

   Private Sub Form_Timer()
   If Not Me.NewRecord Then
     Forms!Expense![CalcTotal] = _
     Format(DSum("[Amount]", "ExpDet", "[ExpID] = " & Forms!Expense![ID]), "Currency")
   Else
     Forms!Expense![CalcTotal] = Null
   End If
   End Sub

4. Disable the Timer() Event whenever appropriate by setting the TimerInterval Property to 0, namely:
   

   Me.TimerInterval = 0

5. Every second, the value in [CalcTotal] will be accurately updated unless you are on a New Record in the Main Form. Once this Record is saved however, the code will be active again.
6. Download the Attachment to get a better picture of what is going on.
7. P.S. - The value displayed in [CalcTotal] should be displayed as Currency, which the code allows for.