INSERT statement conflicted with the FOREIGN KEY constraint 2

[Lost500]

Hi everybody,
I have a SQL db that i recently upsized from Access. my table relationship worked fine in Access but i'm having trouble in SQL.

my table structure includs one "Main" table with a primary key field that was related to three "Sub" tables all connected on the primary key and all three are one-to-many with the "Main" table.

Now that the db is on SQL (I am still working in Access with linked tables) when i go to add a record to one of the "Sub" tables via subform i recieve the following error

'The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblTitleInfo_tblTractInfo" The conflict occurred in database "GMXPropertyBaseSQL", table "dbo.tblTractInfo", column 'TractName', (#547)

TractName is the primary key field on tblTractInfo which is the "Main" table. I didnt' think that i was inserting any records on that table, I would think that the record is being inserted into dbo.tblTitleInfo, the sub table.

Please shed some light on me its so very dark here.

Thank you!
Lost

 

[pjm]

Believe you are not populating the FK in the new child record before trying to commit it. The "Before Update" event is usually a good place to do this.

HTH
pjm

 

[Lost500]

thanks pjm,

to clarify, if the join is on the field "TractName" and i try to add a record to the "Sub" table it need to have the correct "TractName" value before it commits? so i could add in the before update that the TractName value for the SubForm equals what the TractName value is on the main form? is this a workaround or something that i always need to do? i feel like it has worked in different cases without it. Thanks for the help!

 

[pjm]

That's correct.

You have a constraint: you can't add a record to the child table unless the TractName column exists in the parent table.

You would generally need to relate the child record to the parent anyway, even if you had no constraints. Use your form's before update event to set the TractName column to the correct parent value.

HTH, good luck,

pjm

 

[Lost500]

Thanks real quick should i be using the subform's before update? and how do i get a value from a field on the main form it doesn't recognize the form when i writing the code for it like:

TractName.value = frmMainForm.TractName.value

maybe this isn't right thanks for the help i feel like i'm getting much closer to the problem.

thanks

 

[pjm]

In the subform's before update event - something like this

[red]me.[/red]tractname.value = frmmainform[red]![/red]tractname.value

If this doesn't work, step through the code and post the exact code line and the error message you get...

pjm

 

[dhookom]

Lost500,
You haven't mentioned anything about your Link Master/Child properties of your subforms which typically should pull the value of Tractname from your main form into your subform. I would make sure this is set correctly and then possibly display the Tractname field on your subform but make it locked and disabled.

Duane
Hook'D on Access
MS Access MVP

 

[pjm]

My apologies, I missed that.

Lost500 - follow Duane's advice and use the Master/Child link fields and Access will do it all for you.

pjm

 

[Lost500]

dhookom,
I cannot find where to view my Master/Child properties but i did specify to link by TractName when i inserted the subform. also i belive it is working because it displays the correct records in the subform for each record in the master form. I deleted and re-inserted the subform making sure to link on TractName and when i go to add a record on the subform i am still getting the SQL foreign key constrant error message. is there something in the table relationship on the SQL side that i need to change. I dont' see all the options that access has but i do have enforce referential integrity set to yes and update set to cascade.

pjm i'm getting the error message 'Object required' 424.

Me.TractName.Value = frmTitleSub2!TractName.Value

the code recognizes when I type me.TractName. it gives me my options and i choose .Value so i think that part is working but when I type frmTitleSub2!TractName. it doesn't pop up my options so maybe that where my problem is

Thanks for the help yall i really need this thanks!

 

[pjm]

Master/Child properties are on the Data tab of the Subform's properties.

You won't get the autocomplete options when using the [red]![/red] operator, but you need to include the forms collection in the reference: [red]forms![/red][frmmainform]!tractname.value.

I'm getting it all wrong today. Take my advice & listen to Duane

pjm

 

[Lost500]

ok so i'm using access 2007 and in the properties of the subform i do not see master/child options? but i can see the correct records in the subform for each record on the main form so doesnt' that mean that the connection is there?

even still i am getting the foreign key contstraint error when i try to add records. is there anything on the SQL relationship that may be keeping me from adding records? does it matter that i used an existing form for my subform? and also this "Main" table has three "Sub" tables connected to it could that be causing problems?

Thanks for all the help
Lost

 

[Lost500]

dhookum,
i just want to say thank you for helping me. I have resolved the issue which was a constrant i had on the relationship between the tables

Thanks
lost