By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,367 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

New record results in blank foreign key in subform

P: 76
Hello,

I'm hoping someone can help me. I have a mainform based on a table (let's call it tblMain) with a Primary Key of [ID]. There is a subform on this main form based on another table (let's call it tblSub). There is a foreign key field in tblSub called [Master ID]. The Primary and Foreign keys are in a One-to-One relationship i.e. for each record in tblSub, there will be exactly one record in tblSub.

I am having a problem where when I add a new record in the main form, a record is added in tblSub with a blank [Master ID] field. If I set the required property on the [Master ID] field in tblSub to Yes, I get error messages. (You cannot add or change a record because a related record is required...). If I take out the default value for this field, I get "The field cannot contain a null value because the Required property for this field is set to True.

This is only a problem on new records. If I go into existing records in the main form, everything appears to work fine. I have the "Link Child Fields" and "Link Master Fields" on the subform set correctly. Any ideas?

Thanks,
Josh
Dec 15 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Josh

Open the relationships window. Click on the link between the two tables (put the no duplicates back on [Master ID] first. Now tick all the cascading boxes and click ok. Do you get any errors.

Mary

Hello,

I'm hoping someone can help me. I have a mainform based on a table (let's call it tblMain) with a Primary Key of [ID]. There is a subform on this main form based on another table (let's call it tblSub). There is a foreign key field in tblSub called [Master ID]. The Primary and Foreign keys are in a One-to-One relationship i.e. for each record in tblSub, there will be exactly one record in tblSub.

I am having a problem where when I add a new record in the main form, a record is added in tblSub with a blank [Master ID] field. If I set the required property on the [Master ID] field in tblSub to Yes, I get error messages. (You cannot add or change a record because a related record is required...). If I take out the default value for this field, I get "The field cannot contain a null value because the Required property for this field is set to True.

This is only a problem on new records. If I go into existing records in the main form, everything appears to work fine. I have the "Link Child Fields" and "Link Master Fields" on the subform set correctly. Any ideas?

Thanks,
Josh
Dec 16 '06 #2

P: 76
Hi Josh

Open the relationships window. Click on the link between the two tables (put the no duplicates back on [Master ID] first. Now tick all the cascading boxes and click ok. Do you get any errors.

Mary
Hi Mary,

I did as you asked. The boxes were already checked. I also tried taking out the Required property on [Master ID]. This still results in new records with a blank [Master ID] field. With non-new records, there is no problem. The records are created in the sub form just as they are supposed to with tehe [Master ID] field linked to the main table's [ID] field. Any other ideas?
Dec 17 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary,

I did as you asked. The boxes were already checked. I also tried taking out the Required property on [Master ID]. This still results in new records with a blank [Master ID] field. With non-new records, there is no problem. The records are created in the sub form just as they are supposed to with tehe [Master ID] field linked to the main table's [ID] field. Any other ideas?
It's very strange and shouldn't be happening.

1. Is the [Master ID] field on the subform (hidden is fine)?
2. Check the control source property of [Master ID] field ?

Mary
Dec 17 '06 #4

P: 76
It's very strange and shouldn't be happening.

1. Is the [Master ID] field on the subform (hidden is fine)?
2. Check the control source property of [Master ID] field ?

Mary
No, it was not. Neither was the [ID] field on the mainform. Once I added, them, it worked fine. I didn't think it was necessary to have those fields on the forms. Thank you for your help.
Dec 17 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
No, it was not. Neither was the [ID] field on the mainform. Once I added, them, it worked fine. I didn't think it was necessary to have those fields on the forms. Thank you for your help.
You're Welcome.

The join field has to be on both the forms but they can be made invisible if you don't want the user to see them.

Mary
Dec 17 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.