Solved Data Source of Form not Updateable (1 Viewer)

[Kayleigh]

I have a simple data entry form which is based on a query joining three tables. However when attempting to input data it restricts data entry to two of the tables. Any ideas how I can resolve this?
DB attached.

 

[theDBguy]

Hi. It's best practice, and you'd be better off, if you design your forms to work off single table source only. It's simpler and less troubles.

 

[Kayleigh]

I understand so how would you recommend I go about adding data to a form such as this one, where I would like a few fields from each table?
Subforms can end up quite messy...

 

[MajP]

I understand so how would you recommend I go about adding data to a form such as this one, where I would like a few fields from each table?

The design does not make any sense to me. I assume a client can have many orders. I would expect to see a main form with client info and a subform for oders.

 

[MajP]

Also I do not understand the Address table. Not sure what that represents. A client has many addresses and an address can have many clients?

 

[Kayleigh]

There is an official order form consisting of three separate pop-out forms which is used to register client, address and orders. The purpose of this form is just to log any enquiries which are called in - so this form takes down basic info, then when client confirms they would like to go ahead with the order, they are taken to original form to complete the registering process. The data provided previously is pre-filled into the official form.

Client has a billing address, address table is sites where order is shipped to - can be multiple.

 

[MajP]

The reason this does not work is because you would have to create foreign keys in the joined tables. Normally done with a subform/s. You can do it without a subform (no one does this) by creating a relationship between the tables and enforcing referential integrity and doing a left outer join.

If a client can have more than one address and more than one order this design is wrong and you need a main and a sub/s. If not you will retype the same client, same address over and over.

 

[Kayleigh]

I will have a lookup combo to current clients. But if it means changing the relationships design, I will stick to using subforms instead.
Thanks for explaining

 

[Pat Hartman]

I agree with Maj. Either there is something we don't know or you have a logic error in the design. You can make the forms work if you use a mainform with two subforms. But the logic error is still there.

Yes, a customer will have one billing address but could have multiple shipping addresses. Your design indicates that each order goes only to a single address and contains only a single item. Although it is rational to make one shipping address per order, it is very constraining to limit an order to one item. Is that really how your business works?

A rational design for the form assuming you really only ever have one item per order, would be a main form for the customer a subform for addresses and a separate subform for the order. The order subform will have a combo from which it picks the shipping address.