Can't Update Record from Select Query

[duffs]

Hi,

I have Access 2002 on Windows XP.

The last version of Access I've used was 97 but I'm getting back into it. I've read a couple of things that recommend creating a form based on a query, not a table, especially if a calculated field is involved.

When I create a select query based on 1 table, I can change/add/delete records right in the results of the select query, which will carry over to the form just fine.

However, when I use an additional table and join them in my select query, I can no longer update any of the fields that show in the query result. The link I'm using is just a 1 to 1.

How can I get around this? I'm using the second table just for lookup purposes (use the value of one of the fields in a calculation), but I want to be able to update the fields from table 1 from the form.

Thanks.

 

[neileg]

Sounds like the second table should not be in the query, but should form the basis of a combo box bound to the relevant field from the first table.

 

[duffs]

When I say "lookup purposes," I mean for a calculation. For example,
Table 1 contains Number of Hours, and Category.
Table 2 contains Charge Per Hour and Category.

So if the user enters:
Number of Hours: 3
Category: CN

And table 2 has:
Catetory: CN
Charge Per Hour: 110.00

I want to show on the screen when the person enters 3 that the charge is 330.00.

I got around this using DLookup in a function the query calls to find out the charge amount instead of a joined table, but it seems like there should be an easier way. If having a form based on a query is a standard thing, it seems to me there should be a way to let the user update fields even if two tables are joined in the query, don't you think?

 

[neileg]

What you suggest should work, either with a calculated field in the query or the calculation in the form.