Form won't calculate totals

[Justin.ITPro]

What I am in need of relates to the SubTotal on the Work Order Form screen. If you are testing the database: 1. Open Main Menu form 2. Select Tina Rich from the Contact Dropdowm and click Details. 3. Work Order screen is displayed with correct totals.

However if you select any other Contact $0.00 shows up for all totals and doesn't calculate from the Work Order Services / Parts SubForms. I am not sure what is causing this issue and need some help... I have attached the referenced database to this thread. [REMOVED... Solved]

[June7]

Tina Rich is the only one with data in Parts Total query. This means everyone else has Null for the field in the form RecordSource and simple arithmetic with Null results in Null.

Really should not save calculated data, especially aggregate data like Parts Total. Should be calculated when needed.

[Justin.ITPro]

Thank you... I have had this problem for 4 days now and you are the first person to actually take the time to look at it and give me a real answer. I have posted this issue on about 5 different forums with no luck. I updated my SubTotal code to:

=Nz(DLookUp("[Services Total]","[Services Total]","[WOrderID] = '" & [txtWOrderID] & "'"),0)+Nz(DLookUp("[Parts Total]","[Parts Total]","[WOrderID] = '" & [txtWOrderID] & "'"),0)

How else can I setup my tables to calculate these totals... I figured this was the best way.

Anyway, Thanks Again!

[Xipooo]

Frankly I'd ditch all the DLookups and calculations and put all of your results into a query, then base the fields on the results of the query. It will be easier to handle that way and although not directly relevant to your situation, I've seen some calculated field not work in a form simply due to a bug in Access.

[June7]

Xipooo, don't think that will work for the aggregate calcs on form.

Justin, the Work Order has subforms using tables as SourceObject. Use forms instead and summary calcs can be done in form footer section of each. Then the main form can reference the textboxes on the subforms. That should probably eliminate the DLookups to the aggregate queries.

[Xipooo]

Xipooo, don't think that will work for the aggregate calcs on form.

Justin, the Work Order has subforms using tables as SourceObject. Use forms instead and summary calcs can be done in form footer section of each. Then the main form can reference the textboxes on the subforms. That should probably eliminate the DLookups to the aggregate queries.

Nah, I mean do it on the underlying data. You can do a dlookup on a specific query that grabs the totals for you or even do the math as VBA in the background looking at the forms record source for its data.

But it sounds like you've got him on the right track.