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.

 

[xenou]

What's the actual SQL for the query that works? (The whole thing).

 

[domtrump]

What's the actual SQL for the query that works? (The whole thing).

SELECT Table2.JP, Table2.Code, Table1.mult, [Table1]![mult]*[Table2]![JP] AS Expr1
FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.Code;

Where JP is my Amount, Code is my TaxCode and mult is my TaxRate

This query correctly calculates JP*mult based on Code. I just cant display this value in a form. Seems like this would be pretty basic - what am I missing?

Thanks

 

[xenou]

With a form, you need to tell it what records will serve as its data source.

In this case, you could do either of the following:

1) Use your query as the source.

This can be done in two ways.
a. Save the query (let's call it Query1) and then use it as the Record Source (in form properties, look for Record Source on the Data tab and then type in the name if the query Query1
b. Just use the SQL. So also in form properties, look for Record Source on the Data tab and then type in the SQL: SELECT Table2.JP, Table2.Code, Table1.mult, [Table1]![mult]*[Table2]![JP] AS Expr1
FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.Code;

If you take this approach you won't calculate the field - the query did that. So you just use the name of the calculated field as the control source (in this case, Exp1). You can usually just drag and drop fields into the form when they are part of the record source.

2) You could use a slightly different query to source the form.

This would be like 1.b. above, but with the SQL instead of: SELECT Table2.JP, Table2.Code, Table1.mult FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.Code;

Now you could add a control and as its control source enter:
=[mult]*[JP]
I usually just type this right into the control. Has an equal sign first.

3)
If you don't set the control source, I don't think this can work unless you get more granular (so there's only one return value). In principle you could use a query as the source of a textbox control but it would have to be a query that returns a single value.

Hope that makes sense ... !

 

[domtrump]

With a form, you need to tell it what records will serve as its data source.

In this case, you could do either of the following:

1) Use your query as the source.

This can be done in two ways.
a. Save the query (let's call it Query1) and then use it as the Record Source (in form properties, look for Record Source on the Data tab and then type in the name if the query Query1
b. Just use the SQL. So also in form properties, look for Record Source on the Data tab and then type in the SQL: SELECT Table2.JP, Table2.Code, Table1.mult, [Table1]![mult]*[Table2]![JP] AS Expr1
FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.Code;

If you take this approach you won't calculate the field - the query did that. So you just use the name of the calculated field as the control source (in this case, Exp1). You can usually just drag and drop fields into the form when they are part of the record source.

2) You could use a slightly different query to source the form.

This would be like 1.b. above, but with the SQL instead of: SELECT Table2.JP, Table2.Code, Table1.mult FROM Table1 INNER JOIN Table2 ON Table1.Code = Table2.Code;

Now you could add a control and as its control source enter:
=[mult]*[JP]
I usually just type this right into the control. Has an equal sign first.

3)
If you don't set the control source, I don't think this can work unless you get more granular (so there's only one return value). In principle you could use a query as the source of a textbox control but it would have to be a query that returns a single value.

Hope that makes sense ... !

hmmm.. maybe I should back up a bit. this is a data entry form for the user. The record source cannot be a query as I understand it if data needs to be written to a table. The user is entering records into Table2. Based on what they select for Code I want to calculate JP*mult so that the Form shows what the amount will be. Of course, when I actually export the data or write it to a report I will simply use a query. But I want the user to see, on the screen, in real time, what the result will be as they enter a value for JP.

Does that clear things up a bit? Note: trying to add a text box to the form and using the Expr1 from the query as the control source results in the same #Name? error. I have googled this and not found a scenario where this error is applicable to what I'm trying to do.

Thanks for any assistance. Starting to get obsessed with solving this

 

[xenou]

Hmmm...
Well, you can use any expressions involving multiplication of fields that don't yet exist in the tables.

Is the user entering the data needed for this calculation? In that case, you want to use the form data for the expression.
For example, if user enters 5 in txtAmount and .10 in txtTaxRate then you could have a form field that is: =[txtAmount]*[txtTaxRate] But we assume these fields are on the form, not in a table. And of course it will be trash until a user actually enters some values that can be calculated. Probably I'd use VBA to code an after update event when the data is entered that's actually needed (this would, for instance, allow you to look up the tax rate after the user enters the amount).

Clear as mud?

 

[domtrump]

Clear as mud?

Pretty much... I'm still not quite following. The user is entering the Amount and selecting the Code from a drop down. I need to calculate TaxDue based on the user entered amount and the TaxRate associated with the selected Code (from Table1).

Are you saying that I can't use a field that exists in another table in a multiplication calculation?

Please expand. Thanks.

 

[xenou]

Hmmm...
Well, you can use any expressions involving multiplication of fields that don't yet exist in the tables.

I meant to saycan't

Your original post was a query. That uses data from tables. Now you say you are getting a value from a data entry form. So that data has not been committed to any tables.

You should back up and say what mult is, what JP is, what is being entered on the form by the user, and what result you want to achieve. Provide an example.

 

[domtrump]

OK, when I get home from work tonight (6PM EST), I'll put together a simple example so that we can make sure we are on the same page. Maybe I'm making this overcomplicated. Thanks for your help!

 

[xenou]

NP. I don't know if it's easy or not ... I sure get confused with forms all the time.