Solved

DSum calculation on main form from a subform DLookup field.

Posted on 2008-06-23
6
714 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:jdol2949
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21848395
see this link

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

Author Comment

by:jdol2949
ID: 21848942
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

0
 

Author Comment

by:jdol2949
ID: 21849143
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?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:jdol2949
ID: 21849729
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.
0
 

Author Comment

by:jdol2949
ID: 21856218
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.

0
 

Accepted Solution

by:
jdol2949 earned 0 total points
ID: 21856425
meh.. I think I got it again.
I moved a text box(tbxSubtotal) to the sub form footer and gave it this control source:
=Sum(DLookUp("Price","Item","ItemNmbr = '" & [ItemNmbr] & "'")*[ItemQty])

Then, I just simply referenced the above text box to the text box on the main form that displays the total:
=sfmViewParts!tbxSubtotal

Voila!  I now have a continuous subform that displays the prices of the selected items automatically, and once those prices are shown, then the quantities and DLookup prices are multiplied in the form footer, then displayed as a Total on the Main page.  Sweeeeet.
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question