Another relationship question

Hi All,

Okay…I hope somebody can help me here…I’m beginning to chase my tail a bit I think and might be overthinking.

If I have a relationship defined from main form to subform…Do I have to show the main ID and sub ID on my form in 2 places… In this case…I have a main grouping and a sub grouping on my form. Do I need to place the main ID among the sub grouping on the form and the sub ID among the main grouping on the form… Or do I have to show either on the form and just link in the relationship window and leave the form with no entry avaliability?

Thanks…I’m pulling my hair out! YIKES!

Cay you attach your file? If it is shareable.

The link between the Master form and the Slave subform form is independent of the various controls on the form. As you know, if you go to the Form Properties for the Slave subform you will see blanks to populate with this information regardless of whether there are controls on the forms that display those fields on the forms.

As for best practices, this is what I find most functional: be very careful of the situations when you allow the user to change the fields that are used as the link between the forms. That can result in unexpected behavior: a row in the subform will disappear when reloaded (because the child field was changed, etc).

The Master field also should be pre-populated, before you open the form, or else you might wind up with a similar situation, where the Slave or Child field will not be automatically updated with the linking data, but will have other information if you enter it, which subsequently will not be displayed with the same master form. Generally, if you are not extensively enforcing the integrity of the relationship through Macros, the Master field should be a Auto Inrement Primary Key, and the Slave or Child field should be made unavailable to be modified in the context of the form (meaning there is no control that displays it, or that control is not Enabled).

(if this answers your question, please accept the answer by clicking the check (image description) to the left)

All that Doug says is sound advice

To answer your questions directly you DONT have to show the main ID and sub ID in two places.

BUT, and most importantly, the main ID smust appear on the main form and the sub ID must appear on the sub form WHEN EDITING the design, otherwise things will not work.

However you can as a matter of good design practice, provided the IDs are NOT meaningful and not necessary to be shown, make them invisible. That way there’s no danger of them being mis-used by your users. If you have to make them
visible them I would take Doug’s advice and don’t enable them.

Again it’s good practice to enforce referential integrity with relationships and with care make use of cascaded updates and deletions. You don’t necessarily need to use macros for this. You can make use of menu option TOOLS → RELATIONSHIPS . Right click on the link between the two fields in the tables and select Edit… and you will be presented with a form that has Update and Delete options for the relationship. Used carefully these can save you a great deal of time, effort and concern over keeping your data correct. However I would suggest that you may well want to warn your users particularly if you make use of cascading deletes.