Seemingly simple?!--how to add records

[Accesser]

Hi there,

I have a join table, JtblAmounts, linked to another table, tblLegal, in a one-to-many relationship (ref integrity is set). The primary key of JtblAmounts is AmountsID (autonumber), which is a long integer number foreign key in tblLegal.

I have a form based off of tblLegal, and when I input data and try to save or go to the next record, a msg window pops up saying "You cannot add or change a record because a related record is required in table 'tblAmounts'."

Well, I know about setting fields as required, and the foreign field AmountsID is not required in tblLegal.

I know about default values, such as '0', and the default value of foreign field AmountsID is null.

I've tried rebuilding the relationship and form.

Somebody please slap me--what am I doing wrong? Any advice/assistance would be greatly appreciated. Thanks,

Eric

 

[JeremyNYC]

Eric,

Ah, but the one thing you don't know about is implication of referential integrity. RI means exactly what that message says--you can't have child records without a parent record. With RI enforced you won't be able to add records to tblLegal without having a related record in JtblAmounts.

Most often this is dealt with by having a form for the parent table with a subform for the child table, linked by the field that represents the parent's PK (a foreign key in the child).

<PossiblyTooMuchInformationMode>
I use those subforms only for displaying information, not for adding, editing, or deleting. In my applications, if you want to do one of those things you open a form for the child record in question, either by double-clicking on a displayed record (for editing or deleting), or by clicking a button on the main form (for adding).
</PossiblyTooMuchInformationMode>

Hope this helps.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[LittleSmudge]

Your fundimental problem is that NULL is NOT a valid value if you want to maintain Referential Integrity.




Have a form bound to table tblAmounts.

Have a subform control on that main form that contains the form you have that is bound to tblLegal

Make sure the LinkParentFields and LinkChildFields refer to the correct field names

Then Access will AUTOMATICALLY populate the tblLegal.AmountsRef ( FK in tblLegal ) with the correct value from tblAmounts.AmountsId when you create a new record in tblLegal.


'ope-that-'elps.




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.