Form/subform and referential integrity

[CPAccess]

How do I maintain referential integrity between a main form and a subform, each based upon different (but joined with integrity enforced) table?

Here's the situation:
I have two tables: tblContracts and tblPayments. tblContracts has an autonumber field called IDKey as its primary key. tblPayments also has an IDKey field (Integer datatype). The two tables are linked in a one-to-many relationship on the field IDKey with referential integrity enforced.

I have a main form based upon tblContracts (the "one" side of the relationship) that has an embedded subform based upon tblPayments. the two forms are linked Parent/Child on the IDKey field.

Here's the problem: If a user goes to a new record in the main form, it allows them to enter information in the subform without entering information in the main form. This means that a new record (and its corresponding autonumber IDKey field value) does not get generated in tblContracts and I have an orphan record in tblPayments that is not linked to any record in tblContracts - which violates the referential integrity that is supposedly enforced between the two tables.

Any guidance on how to deal with this would be greatly appreciated.

TIA,
Carolyn

[comteck]

Everything seems to be set up ok. Check to make sure you are including the IDKEY on both forms. You can even hide those fields if you want, but I'm pretty sure they should be there. Since the IDKEY on the main form is an autonumber, it should then create that same IDKEY on the subform, due to the parent/child link.

comteck

[CPAccess]

That's exactly what I would have thought, but it does not appear to work that way. The tables are linked in the relationships window with referential integrity enforced for both cascade update and cascade delete. The IDKey field appears on both forms and is used to link the main form to the sub form. IDKey is the primary key for the table on which the main form is based.
Anyway, the workaround I am using is to make the subform invisible until something is entered on the main form. Not my first choice for usability, but at least it keeps the integrity of the table relationships.
Any further thoughts would be welcome.
Carolyn

[comteck]

In your "tblPayments" table, is the datatype a Number or an Autonumber? It should be Number.

I'm assuming you don't get a message that says "Index or Primary Key cannot contain a null value."

Like you, I'm baffled with this one.

comteck

[CPAccess]

Hi, again.

The data type of the IDKey field in tblPayments is Integer.
No error message. (Actually, the error message you indicate is the error message I would have expected.)

I've tried setting this up in a bitsy test DB as well as my larger application just to make sure there wasn't something else weird going on.

The test DB has two tables: tblContracts (with two fields: IDKey as primary key, autonumber, and Contracts as a text field) and tblPayments (also with two fields: IDKey as Integer and Payments as a text field.) The tables are set up in the Relationships window as a one (tblContracts) to many (tblPayments) relationship with referential integrity enforced.

Then I created two forms: frmContracts with the two fields from the Contracts table and a subform, frmPayments with the two fields from the Payments table and linked the two together on IDKey.

If you go to a new record in frmContracts and only enter information in the Payments subform, and then close the form, you'll wind up with an orphan record in the Payments table.

It's a puzzlement...

[comteck]

Integer is not a data type. It is selected under "Field Size". The data type can be either "Autonumber", or "Number", and Integer can be selected for either.

While in the table design view, you select a "Field Name", and directly to the right of that, you select the datatype. For the main form, the datatype should be Autonumber, and for the sub-table, the datatype should be Number.

As well, I've always selected "Long Integer", as opposed to "Integer". It shouldn't make a difference though.

comteck

[MMcCarthy]

IDKey should be a foreign key in tblPayments not a primary key. There should be a separate primary key in tblPayments unique to it. Try this and see if it makes any difference