asked on

DSum calculation on main form from a subform DLookup field.

Access 2003-
What I'm trying to do here is create a sum calculation that would total up all of the prices from the 'Price' field in the subform. The only problem is, the prices on the sub form are currently being populated using the DLookup statement. So, when a part is entered into the subform, the corresponding price is 'looked up' and displayed in a text box next to the part number. What I want to do is have a text box on the main form (frmTestView) calculate all of these DLookup prices that appear on the sub form. What would be the best way to go about calculating the Price field in this sub form?

Thanks,

Ryan

Rey Obrero (Capricorn1)

see this link

How to Bring a Subtotal from a Subform to a Main Form
http://support.microsoft.com/?kbid=208998

jdol2949

ASKER

Thanks cap, I followed all of the instructions from that link. I'm still getting a '#Name?' error for the total on my main form. I'm sure it has to do with the fact that the price is being referenced from a server database, and the rest of the subform is actually grabbing information from a local Access table. Is there some sort of command to calculate the sum of the text box that generates the price for each item? below are the two text box controls, and the text box that includes the DLookup statement.

subform txt box control (tbxSubTotal):
=Sum([tbxViewPrice])
 
Main form control (tbxTotal):
=sfmViewParts.frmBOMViewParts!tbxSubTotal
 
Price lookup text box (tbxViewPrice):
=IIf(IsNull([ItemNmbr]),"",DLookUp("Price","Item","ItemNmbr = '" & [ItemNmbr] & "'"))

Open in new window

jdol2949

ASKER

Or, is there a command that calculates the tbxViewPrice column from the subform AFTER the list has been populated by the two combo boxes on the main form?

jdol2949

ASKER

or... is there some sort of OnClick or AfterUpdate event I can set somewhere to display the total after all of the subform fields have been populated? The 'Price' column still wouldn't have a field name, but maybe I could reference the text box it's tied into? (tbxViewPrice)
Thanks.

jdol2949

ASKER

Any comments or suggestions? Can DLookup statements be referenced as a field name, so that I can calculate the sum of all of the prices that are on my continuous form? Please.. any help at all would be greatly appreciated.

ASKER CERTIFIED SOLUTION

jdol2949

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial