Can't assign value error

[Stu]

I have a form with two subforms. The first subform works as expected because
the link field (primary key) is always populated on the main/master form.

The second subform throws the "Can't assign a value..." error on data entry
probably because the link field on the main/master field is not populated.
In fact I only want to populate the link field on the master form if there is
data entered in the corresponding subform. Question:
1) is there a way to trap the "Can't assign a value..." error? If so I
could then handle it.
2) I could do this also with a pop-up form, it that a better method?

 

[ruralguy via AccessMonster.com]

Two questions:
Is the RecordSource of the 2nd SubForm updateable?
Why isn't the MasterLinkField on the MainForm populated yet?

 

[Stu]

1) yes
2) The master table/form field is a Foreign Key to the child. The child will
generate the key upon data entry (autonumber) which then needs to go back to
the master table field as a FK. But most master records will/do not have a
corresponding child record in which case the subform is hidden.

 

[ruralguy via AccessMonster.com]

Usually one would display the many side of a 1:m relation ship in a SubForm.
It sounds like the One side is in your SubForm.

1) yes
2) The master table/form field is a Foreign Key to the child. The child will
generate the key upon data entry (autonumber) which then needs to go back to
the master table field as a FK. But most master records will/do not have a
corresponding child record in which case the subform is hidden.

Two questions:
Is the RecordSource of the 2nd SubForm updateable?

[quoted text clipped - 10 lines]

 

[Stu]

After some further thought I resolved this by using the primary key from the
main form and putting a corresponding FK in the subform. This solves the
error and behavior problem. Then I manipulate, using code, the PK from the
subform as needed. Problem solved.

Thanks for your comments, it got me thinking.

Usually one would display the many side of a 1:m relation ship in a SubForm.
It sounds like the One side is in your SubForm.

1) yes
2) The master table/form field is a Foreign Key to the child. The child will
generate the key upon data entry (autonumber) which then needs to go back to
the master table field as a FK. But most master records will/do not have a
corresponding child record in which case the subform is hidden.

Two questions:
Is the RecordSource of the 2nd SubForm updateable?

[quoted text clipped - 10 lines]

could then handle it.
2) I could do this also with a pop-up form, it that a better method?

 

[ruralguy via AccessMonster.com]

Glad you got it sorted. Sometimes all you need is a sounding board.

After some further thought I resolved this by using the primary key from the
main form and putting a corresponding FK in the subform. This solves the
error and behavior problem. Then I manipulate, using code, the PK from the
subform as needed. Problem solved.

Thanks for your comments, it got me thinking.

Usually one would display the many side of a 1:m relation ship in a SubForm.
It sounds like the One side is in your SubForm.

[quoted text clipped - 10 lines]