Adding a total box to a form

[mdgibbs88]

Good Morning All,

I am creating a database to track gifts to employees. The admin person will fill out a form for each gift and create a report with a total $ value for those gifts for the history of the employee.

I would like to create a box on the form that totals all of the $'s for the employee selected, so the admin can see what has been gifted before they start the next entry. They will select the employee from a drop-down list (Physician_ID) and when that selection is made, I would like to populate the Total_to_Date box on the form with a total of $ gifted for that employee from the NMC_Detail_Records table.

I have attached the small database.

Thanks in advance! Mark

 

[pr2-eugin]

You can lookup on DSum method..

 

[mdgibbs88]

Thanks Paul,

I put in the DSum and it works like this:

=DSum("NMC_Value_Total","NMC_Detail_Records","Physician_ID=2")

but, I want to sum based on a control box on the form called Physician_ID. I try to type it in and the editor keeps changing this:

=DSum("NMC_Value_Total","NMC_Detail_Records","Physician_ID=" & Me.Physician_ID)

to this:

=DSum("NMC_Value_Total","NMC_Detail_Records","Physician_ID=" & [Me].[Physician_ID])

and I keep getting a #Error message

 

[pr2-eugin]

If you are using it directly in the Control Source it should be..

=DSum("NMC_Value_Total","NMC_Detail_Records","Physician_ID = " & [Physician_ID])

 

[mdgibbs88]

Thanks again Paul,

I am getting a #Name? in the control box that has the DSum when I use that code.

I have attached a screen shot of the design view and form view to take a peek at.

Mark

 

[pr2-eugin]

Hmmm.. I just had the same..

=DSum("[NMC_Value_Total]","[NMC_Detail_Records]","[Physician_ID] = " & [Physician_ID])