Updatable Multi table Query (1 Viewer)

meiteniite89

New member
Local time
Today, 22:05
Joined
Aug 13, 2020
Messages
23
Hello,

I have created Form based on 2 tables that allows user to update information. One has all the equipment information and the other one contains all the areas that equipment can be found with additional information about area and responsible people.
I linked both tables using Location Code, unique for Area table, but repeats on Equipment table. Now it won't let me update information on the form.
And I can't figure out how to make it work, if I only use one table everything works, but once both tables are used it won't let me.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:05
Joined
Oct 29, 2018
Messages
21,358
Hi. I suggest you use a form/subform setup instead. Each form will be based on only one table, and you just link both forms using the Location Code fileds.
 

isladogs

MVP / VIP
Local time
Today, 22:05
Joined
Jan 14, 2017
Messages
18,186
I would agree with the DBguy about using a form/subform.
However, this link gives a clear account of the various reasons why a query may be read only http://allenbrowne.com/ser-61.html
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 19, 2002
Messages
42,976
You'll need to show us your schema to see what the problem is. Make sure we can see the PKs and join lines on the relationship window layout.

It isn't the number of tables in a join that makes a query not updateable, it is the way the tables are joined. If you haven't established proper primary and foreign keys, it is quite easy to end up with not-updateable queries. The other common way is to include a query in the join that aggregates data. The first situation sounds more like the problem you are having.

Also, as the others have suggested, it is rarely good practice to allow updates to both sides of a relationship in the same form. If you need to do that, the best solution is to use a mainform/subform as dbGuy suggested.

You might have a form that shows an order. You would frequently include a join to the customer table so you could show customer info on the order form. HOWEVER, even though Access would allow you to update data in both tblCustomer and tblOrder, it is bad practice because it is easy for people to make mistakes. If I place an order with Swiss Colony and I ask to send it to my brother instead of to me, I don't want a change to the ship to info on THIS order to permanently change my name and address in tblCustomer. To prevent that, I always lock display fields from the "lookup" table.
 

Users who are viewing this thread

Top Bottom