Referenciall Integrity Not Working

[CementCarver]

Based on my data model, my member's table is linked to a producer's table via the member_id column. The member_id column in the member table is an auto number and I've updated the member_id column in the producer table, based on the company names being equal. The producer table's member_id column is a type number.



I've created the relationship between the two by dragging the member.member_id to producer.member_id, which is a one to one relationship, and I've selected cascade updates and deletes.

So, if I understand this correctly, if I manual add a record in the member's datasheet view, then that member_id value should self populate in the producer's table?

When I try this manual update in the member table, the updating of the producer's table does not happen.

What am I doing wrong?

CementCarver

[June7]

No, data entry in members table will not create record in producers table. However, as soon as value is entered into any field of producers table, a record is initiated and the member_id will populate.

Better to use forms for data entry. This would be a form/subform arrangement.

Why is this a 1-to-1 relationship? Why separate tables? Will each member always have a related producer record?

[CementCarver]

Thanks for your reply June7. Unfortunately, when I open my member's form and enter in a new record manually, the producers form does not populated with the new member_id. The producers form is linked to the member's form via the open form based on the same member_id value.

So, I'm not sure what I am doing wrong, but the referential integrity set up in my relationship schema is not working.

CementCarver

[June7]

Again, entering a record in Members will not create a record in Producers. Must enter data into Producers to create record in Producers. If the form/subform are properly linked then the member_id will populate when data is entered in Producers. Are the Master/Child links properties of the subform container control set? If you want to provide db for analysis, follow instructions at bottom of my post.

[CementCarver]

Are the Master/Child links properties of the subform container control set? Not sure what you mean here. When I added a command button to my member form, it opens the producers form where the MEMBER.member_id = PRODUCERS.member_id. If that's what you mean by controls, then yes.

CementCarver

[June7]

So you are not using a form/subform arrangement. If you don't then will need VBA code to pass the member_ID to the independent Producers form when it opens to create a new related record.

[CementCarver]

Are there any samples of such code that does this?

[June7]

More than one way to do it. This is a fairly common topic in forum.

Try VBA Code in the Producers form Current event procedure:

1. use the OpenArgs argument of DoCmd.OpenForm to pass the ID value then code behind Producers form can extract from the OpenArgs property
If IsNull(Me!MemberID) Then Me!MemberID = Me.OpenArgs

2. code behind Producers form references Members form Member_ID value
If IsNull(Me!MemberID) Then Me!MemberID = Forms!Members!Member_ID

Really, the simplest approach is a form/subform. No code required.

[CementCarver]

THANKS June7. Appreciate your return comments.

[CementCarver]

I'm new to coding so I really don't have a clue what you mean when you stated to use OpenArgs argument of DoCmd.OpenForm to pass the ID value.

Any chance you can direct me to a link/forum discussion that has more complete code samples?

CementCarver

[June7]

OpenArgs is an argument of OpenForm/OpenReport methods use with DoCmd.

At a minimum, the usage would be like:

DoCmd.OpenForm "form name"

Review Access/VBA Help or Google to get more info about arguments and parameters associated with those methods.