Problem with multiplying value of two fields from different tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dwal - 04 Apr 2008 22:23 GMT

I am trying to multiply two fields that are in a different table and show the
result in my orders form.

The first Table is called Orders and has the following fields:

Customer ID
Order Number
Item Number
Date
Quantity

The Items Table consists of:

Item Number
Item Description
Price
Quantity Available

In my Orders Form, I have the same as in the Items table including Totals.
In the totals field i set control source:

= [Items]![Price]*[Quantity]

As a result i get this in the totals field: #Name?

I set the Price field in the Items Table to Currency

&

Quantity Field in the Orders Table to: Number

I am a newbie in MS Access, and i am currenly working on an assignment for
my school.

v/r

David

Reply to this Message

Allen Browne - 05 Apr 2008 04:25 GMT

Create a query that uses both tables.
Use this query as the RecordSource for your form.
You can then get the values from both tables into your form.

The approach you are using bothers me, though. Is there any chance that one
of the items might change price in the future? If you do change the price,
won't that mess up all your existing orders?

For that reason, you might consider including the UnitPrice in the Orders
table. You can use the AfterUpdate event procedure of the [Item Number] to
look up the current price for you. The Northwind sample database has an
example of how to do that (in the Orders Subform.)

Another suggestion: Could one customer order contain multiple items? If so,
see how Northwind handles that by using an Order Details table so one order
can have many rows.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I am trying to multiply two fields that are in a different table and show
>the
[quoted text clipped - 30 lines]
> I am a newbie in MS Access, and i am currenly working on an assignment for
> my school.

Reply to this Message

Homer J Simpson - 05 Apr 2008 06:32 GMT

> I am trying to multiply two fields that are in a different table and show
> the
[quoted text clipped - 21 lines]
>
> As a result i get this in the totals field: #Name?

Should it not be [Items]![Price]*[Orders]![Quantity] ???

Reply to this Message

Bob Quintal - 05 Apr 2008 13:10 GMT

>> I am trying to multiply two fields that are in a different table
>> and show the
[quoted text clipped - 23 lines]
>
> Should it not be [Items]![Price]*[Orders]![Quantity] ???

Prefixing with the table name is only required when there is
ambiguity in which field is addressed. In a query with the above two
tables, [Item Number] appears in both so Access must be told which
of the fields the programmer wants to use. If there is no ambiguity,
because the field name is only in one table, Access doesn't need the
table's name.

However, in this case, I suspect that the original poster built the
form on the Orders table only and is trying to reference the value
from a table that's not in the recordsource. Both tables should be
in the recordsource, using a query, or the value could be referenced
using a dLookup() call

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

dwal - 07 Apr 2008 11:37 GMT

First of all, I would like to thank for the quick response. I applogize that
I was not able to answer earlier.

The Northwind Example is helping me alot in understanding how it all works.
When you first said Northwind I had no clue what you were talking about but
thanks to Google and MS Help I was able to figure out what you meant :)

To Bob: Yes, you are correct. I did try tp built the form on the Orders table.

V/r

David

Reply to this Message