Form Calculation using multiple tables

[domtrump]

I have two tables:

Table1 has two fields: Amount,TaxCode
Table2 has two fields: TaxCode,TaxRate

There is a relationship established between the tables. If I build a query and add a calculated field of [Table1]![Amount]*[Table2]![TaxRate], the calculation is performed correctly.

However, if I create a form, add a text box and enter the above calculation in the Control Source, I get the #Name? error. What am I doing wrong?

Thanks.

 

[domtrump]

OK, lets say I have the following tables:

Produce: Fruit, Price
Orders: Fruit, Quantity

The relationship is Produce (1) to Orders (many) on the Fruit field. I need a form where the user selects the Fruit from a drop down, then enters the quantity. This data gets committed to the Orders table when the record is saved. On the form, I need to see two controls:

One that shows the Price, based on the selection the user made for Fruit and
One that shows the Order Total (Quantity * Price) based on the selection the user made for Fruit and the Quantity entered by the user. This information just needs to be displayed on the form.

Does that clear up what I'm trying to do?

Thanks.

 

[xenou]

I put this together last afternoon - it may serve as a good enough example. Though I'm no expert in Access form design.

The simplest thing to do is use a DLookup function in a textbox. This is given in Form1 in the sample database. Unfortunately, this is somewhat awkward in a form with only two controls as in our example. The user doesn't interact with the form very much - so just when you'd like to show the extended price (after quantity is entered), the user isn't doing anything else to prompt the update of the control. And with only two controls, you really can't assume that a typical user will go top to bottom rather than bottom to top, though the former is more natural (e.g., if you put quantity first on the form, then the picklist for product).

I created a second form (Form2) that attempts to be more responsive by using the text property of the quantity control. This can be monitored by Access's event model for changes. We then update the values in a hidden form (which I've called qty_helper) and use *that* control in our DLookup formula.

Sample database below:
<a href="http://northernocean.net/etc/mrexcel/20110520_db.zip">SampleDB.mdb</a>

Checksums (zip file)
md5: ed901c940ff40f3d5b2fb74cef190f16
sha256: ce1f4384b504e96af9d3983461a5eb11f2a9abfaf2acf4846b5efc634ad0995f