Error when adding a new record to a form

[ksto]

I have a form [fInsurance] based on a query [qInsurance]. The query is based
on two tables [tVendors] and [tInsurance]. While the form will allow me to
update records, it will not allow me to save new records. The new records
button is not grayed out so I can go to a new record and enter all the data.
However, when I try to tab out of the new record, I get the error message
"You cannot add or change a record because a related record is required in
table "tInsurance".

I have checked the properties of the form and Allow Additions is set to yes.
Any ideas on what the problem is?

Thanks to all of you gurus who spend your time helping people like me!

 

[ksto]

The tInsurance and tVendor tables have a one-to-one relationship, with
VendorID as the primary field in each table. I then joined my tables
together with a query, on which the form is based. Here are the options I'm
aware of:

1. Join tInsurance and tVendor into a single table
2. Split the form into form/subform with one based on each table

I guess my question is, is there any way to add records to both tables from
the form without changing the existing structure?

As the error states: you are not adding a record to the tInsurance table
which sounds like it is the Parent side of a Parent/Child relationship
between the two tables.

I have a form [fInsurance] based on a query [qInsurance]. The query is based
on two tables [tVendors] and [tInsurance]. While the form will allow me to
update records, it will not allow me to save new records. The new records
button is not grayed out so I can go to a new record and enter all the data.
However, when I try to tab out of the new record, I get the error message
"You cannot add or change a record because a related record is required in
table "tInsurance".

I have checked the properties of the form and Allow Additions is set to yes.
Any ideas on what the problem is?

Thanks to all of you gurus who spend your time helping people like me!

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ksto]

No, it's not an autonumber. VendorID is a text field imported from our old
database and used as the primary key in both tables.

Do you really have VendorID as the PK on *both* tables? Is it an AutoNumber
in one of the tables?

The tInsurance and tVendor tables have a one-to-one relationship, with
VendorID as the primary field in each table. I then joined my tables
together with a query, on which the form is based. Here are the options I'm
aware of:

1. Join tInsurance and tVendor into a single table
2. Split the form into form/subform with one based on each table

I guess my question is, is there any way to add records to both tables from
the form without changing the existing structure?

As the error states: you are not adding a record to the tInsurance table
which sounds like it is the Parent side of a Parent/Child relationship

[quoted text clipped - 12 lines]

Thanks to all of you gurus who spend your time helping people like me!

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ksto]

I think I figured it out:

I just went into Edit Relationship for the two tables and set "Cascade
update related fields" to yes. It will now allow me to update and append
records to the two tables.

Thanks for your help - you got me thinking along the right lines!

I've not worked much with 1:1 relationships but you will undoubtedly need to
fill in the PK key of one of the tables yourself - and it looks like it is
the tInsurance table. I would try using the BeforeUpdate event of the form
for this.

No, it's not an autonumber. VendorID is a text field imported from our old
database and used as the primary key in both tables.

Do you really have VendorID as the PK on *both* tables? Is it an AutoNumber
in one of the tables?

[quoted text clipped - 15 lines]

Thanks to all of you gurus who spend your time helping people like me!