Fill in total from subform and one other question

[Steel Bananana]

Good morning all!
First, I am VERY new to Access, but have taken the 'basics' courses several
times. I understand enough to customise the windows templates, but in no way
could create my own from scratch yet! In fact, the one I'm having questions
about is the Customer's Orders Database from the MS website.
In the 'Orders' form, there is also an 'Orders Details' subform.
1. How can I get it set up so that once I select a product from the dropdown
of available products, the Unit Price automatically fills in? I'm not sure
why it doesn't do this to begin with, but I'd love to figure out how to do
it, or the DB is virtually useless.
2. Can I have a Box in that form somewhere that totals the orders? I have
inserted a unit cost into the Table, so I would also love to get a 'Total
Profit' box as well. I'm assuming that I want these boxes in my 'Orders'
table since it really doesn't pertain to the 'Order Details' or 'Products',
however I'm not sure of the correct code to get the totalling action to
update as I add items to the product details subform!

I'm HOPING that this made sense! I'm fairly experienced in HTML and have
done some custom coding in Adobe, so the abstract of the code isn't a
problem...it's where to begin!

Thanks so much!

 

[Allen Browne]

Open the Northwind sample database that installs with Access.

Open the Orders Subform in design view.
Right-click the ProductID combo, and choose Properties.
On the Event tab, click beside the AfterUpdate property.
Click the Build button (...) to the right of the property.
Access opens the code window.
You can now see how the code performs a DLookup() to get the UnitPrice from
the Products table for the ProductID chosen in the form.

If you need further help with DLookup(), see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

Regarding the total, you can see the text in the Form Footer of the Orders
Subform in Northwind. Now open the Orders form in design view, and you will
see how the main form reads the total from the subform and displays it on
the main form.

Note that you must *not* store the total in the main form's table.

For more information on why that is so, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html
The idea of not storing dependent data in your tables is an absolutely
crucial concept for good database design.

 

[Steel Bananana]

I'm going to have to go on the articles alone. I'm using 2003 on a work
computer, and for some reason, though I have several 'default' dbs, I cannot
find one with 'Northwind' in it. I've even searched my c:/ for .mdb.
Thanks for the links though! Once I get through all of them, we'll see how
I make out. If I need more help, I'll give you another holler!

Thanks again,
Kim

 

[Allen Browne]

The file is called northwind.mdb.

It typically installs to:
C:\PRogram Files\Microsoft Office\Office\Samples