Problem with multiplying value of two fields from different tables

[dwal]

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

 

[Allen Browne]

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.

 

[Homer J Simpson]

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?

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

 

[Bob Quintal]

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?

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

 

[dwal]

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