Form won't calculate totals

[Justin.ITPro]

I am having trouble on the Work Orders Form to get the SubTotal to calculate correctly.

The SubTotal Control Source is:

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

The Form Record Source is:

SELECT DISTINCT [Work Order].*, [Payment Total].[Payment Total], [Services Total].[Services Total], [Parts Total].[Parts Total] FROM (([Work Order] LEFT JOIN [Parts Total] ON [Work Order].WOrderID = [Parts Total].WOrderID) LEFT JOIN [Payment Total] ON [Work Order].WOrderID = [Payment Total].WOrderID) LEFT JOIN [Services Total] ON [Work Order].WOrderID = [Services Total].WOrderID;

Can anyone help me understand why my form won't calculate totals?

 

[pr2-eugin]

Try moving the DLookup into FormCurrent method instead of setting the Control Source. Also remember that DLookup will return a Null if the criteria is not met. Warp them using Nz function.

 

[Justin.ITPro]

I couldn't get that to work... I think that maybe my relationships got messed up or deleted and caused the form to "break" because it was working correctly about a week ago. I am just not completely sure and don't want to risk messing things up further.

I have attached the database file if you wouldn't mind taking a look at it.

The problem is occurring with the Work Order Form and the SubTotal field.

EDIT: If you look at the Client Tina Rich and run the Work Order, everything works, but not with the other clients.

Thanks