Problem with Booking form with subform -Can't enter a new record

[agiuga]

I have created a booking form based on a query which has Booking table, Customer table, Dog table, Treatment table and a bridge BookingTreatment table (the subform). This table has a booking and Treatment composite primary key. As you can see the form won't let me enter a new record for an existing customer and dog id, which will update the booking table and bridge. It will show existing booking details. The treatment ID is taken from the bridge. It's probably simple but I can't see the answer. Any help would be great.

[RuralGuy]

Perhaps this link will explain your situation: http://allenbrowne.com/ser-61.html

[agiuga]

Perhaps this link will explain your situation: http://allenbrowne.com/ser-61.html

Thanks. Nothing obvious. The form will allow me to enter non primary key fields.

[NTC]

there is no need for composite keys. You state there is a Booking table but those fields do not appear to be sub form fields, so I will presume you've joined them in a query and this query is not updateable. Instead work directly with tables, and use multiple sub forms inside your main form.

In general: redo your primary table so there is one 1 key field, and it is an autonumber. That could be one of your current ID fields, I can't say - or it can be an entirely new field. For the moment we'll call it the AutoKey field. If there are other ID fields in this main table you can set their property to Indexed, No Duplicates if you wish, but do not designate them as key fields. If the other ID fields are other tables - use sub forms.

in a table of a sub form - this table must also have its own Autonumber Key field - let's call it SubAutoKey field that is the primary key. It must also have the AutoKey field which is a Number field. Delete your current sub form, and then reset it into the main form and the wizard will set up so that main table's AutoKey will populate into sub table's AutoKey.

[agiuga]

Thanks
I had seen video tutorials where a query is used consisting of multiple tables to create a booking form. Maybe they were designed to view existing bookings rather than create new ones. Do bridge tables normally use its own primary key then rather than composite based on the linked tables?

[NTC]

There is no formal definition of a "bridge" table - so one can't be sure what is meant by that. Composite keys are never needed. They are possible - but never needed - and confuse things. I will guess that you have tables:
* Customers
* CustomersDogs (assuming multiple dogs to a customer)
* Bookings
* BookingSessions (assuming multiple sessions to a booking)

I would think that you would have a Customer Form (with the Customer Dogs as a sub form) stand alone to first enter this information.

The Booking form is somewhat complex. It would have the Booking Sessions as possibly a continuous sub form within it. You would have a selector to select a Customer from among the Customer table and then a second selector that is a cascading combo box that provides a choice of that selected customer's dogs.

[ssanfu]

Maybe these will help you

Read this (at least 5 time - several important concepts here).
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


This also has important concepts - especially this paragraph: Primary Key Should be One Field and Numeric.
Microsoft Access Performance Tips to Speed up Your Access Databases
http://www.fmsinc.com/microsoftaccess/Performance.html