Child / Master subform not inserting link data 1

[1DMF]

Hi,

I have a form which has a sub form, the sub form has a key ARNO and that is linked to the Membership Number on the Main Form, but when I insert a record into the subform datasheet, access isn't putting the link field data (Membership Number) into the subform field (ARNO) for each new record.

Thats what the link is suppose to do isn't it? or am I missing something

 

[RobSindle]

Did you check the Link Master and Link Child properties of the subform control (not the subform itself)(check under the 'data' tab).
WHen there is a proper Primary key foreign key relationship defined between the 2 tables Access automatically provides the field names. If the foreign key on the sub-table is not of the same data type, it will not provide these fields automatically.

(You should check to see if the foreign key in the subtable is the same data type as the primary key in the main table).

HTH.
Rob

 

[1DMF]

Yes the child and master links had the fields in the data tab, all these links i reset manually after embeding the sub form, I re-embedded the subform etc... and yes both fields are same data type (nvarchar) length 50.

It just wouldn't enter the data into the form and to make sure I made the childs field "Allow Null = No".

However neither fields are the "KEY" in their respective tables, I just wanted to create a link between these tables using those fields. Would that make a difference?

In the end I gave up, access just didn't want to do what it was meant to so instead i've set the 'default value' to equal the master field data and hidden the field from the form so it cannot be overwritten.

Look like the usual case of if you want something done properly - do it yourself!

 

[PHV]

neither fields are the "KEY"
The classical way is the Child field is the Foreign Key referencing the Master field which is the Primary Key.
With RelationShips properly set all this referencing is automagically made by the form wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[1DMF]

when I say neither are the "KEY" I mean the tables already have primary keys and use them to link to other tables and those forms work fine, i just wanted to create a link between two tables by just normal fields in the table.

I did go into SQL and add a relationship but that didn't help either, the way I have it is working fine, with the default set to the other forms field which forces the link manually.

 

[RobSindle]

To make a long story short, you CAN easily take care of the insertion for yourself.
In the before-update event of the subform just do something like:

Me.YourForeignKeyField = Me.Parent.PrimaryKeyOnMainform

HTH.
Rob

 

[1DMF]

yup, another way to force it, thanks.

But the question I guess I was really asking was isn't this suppose to be done by Access when you use the subform insert tool. you draw the subform, follow the wizard , provide the child and master fields and access does the rest, well in theory but it didn't and I was curious as to whether it was meant to or is it common practice to have to manually force the referential integrity between non-key fields in a table.

 

[RobSindle]

Not sure, I'd never done an ad-hoc relationship between 2 tables via mfrm / sfrm.
Rob

 

[1DMF]

Hey I like to be different, it's more of a necessity because i'm intergrating two applications and their respective tables, and need to build links and keep referential integrity between the two applications which used to run stand alone (basicaly, our back office and our website members area).

but it's starting to come together nicely