I have set a relationship between the master and child tables.

Customer.ID -> callnotes.Customer_ID

Enforce referential integrity.
Cascade updates.


I am told Access is supposed to put  the customer.ID  value in the 
callnotes.Customer_ID  it is not working


How do I do it manually in VBA code?

"Avid Fan" <me@privacy.net> wrote in message 
news:%236Q8lRRzKHA.3884@TK2MSFTNGP06.phx.gbl...
>I have set a relationship between the master and child tables.
>
> Customer.ID -> callnotes.Customer_ID
>
> Enforce referential integrity.
> Cascade updates.
>
>
> I am told Access is supposed to put  the customer.ID  value in the 
> callnotes.Customer_ID  it is not working
>
>
> How do I do it manually in VBA code?


You don't.  This is controlled by the Link Master Fields and Link Child 
Fields properties of the subform control.  If you created that relationship 
after you added the subform to the main form, Access would not have been 
able to guess that those were the linking fields, so you have to go to the 
properties of the subform control -- the control on the main form that 
displays the subform -- and enter "ID" in the Link Master Fields property 
and "Customer_ID" in the Link Child Fields property (without quotes around 
the field names).

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On Sat, 27 Mar 2010 05:44:44 +1100, Avid Fan <me@privacy.net> wrote:

>I have set a relationship between the master and child tables.
>
>Customer.ID -> callnotes.Customer_ID
>
>Enforce referential integrity.
>Cascade updates.
>
>
>I am told Access is supposed to put  the customer.ID  value in the 
>callnotes.Customer_ID  it is not working

WHat happens when you enter data into the note field?

I have NEVER ONCE seen a master/child link field fail. I think your
expectation is what is faulty: just having the relationship or having the
master/child links will NOT automagically create a new empty record in the
callnotes table. It will only be created when you *actually enter data onto
the subform* - are you?
-- 

             John W. Vinson [MVP]

On 27/03/2010 6:18 AM, John W. Vinson wrote:
> On Sat, 27 Mar 2010 05:44:44 +1100, Avid Fan<me@privacy.net>  wrote:
>
>> I have set a relationship between the master and child tables.
>>
>> Customer.ID ->  callnotes.Customer_ID
>>
>> Enforce referential integrity.
>> Cascade updates.
>>
>>
>> I am told Access is supposed to put  the customer.ID  value in the
>> callnotes.Customer_ID  it is not working
>
> WHat happens when you enter data into the note field?
>
> I have NEVER ONCE seen a master/child link field fail. I think your
> expectation is what is faulty: just having the relationship or having the
> master/child links will NOT automagically create a new empty record in the
> callnotes table. It will only be created when you *actually enter data onto
> the subform* - are you?

I fill data into the memo text box.  I change the one other control left 
  combobox

I even have a save record and move to next record buttons on the forms

I have placed a text box control linked to customer_id  the only way I 
can get the record to save is by placing a valid ID number in the field.

On Sat, 27 Mar 2010 11:48:32 +1100, Avid Fan <me@privacy.net> wrote:

>I fill data into the memo text box.  I change the one other control left 
>  combobox
>
>I even have a save record and move to next record buttons on the forms
>
>I have placed a text box control linked to customer_id  the only way I 
>can get the record to save is by placing a valid ID number in the field.

And this is in fact a Subform in a subform control (not a popup form launched
from code)?

Please post the SQL view of the mainform and the subform's Recorsource
properties and (again) the Master Link Field and Child Link Field properties
of the Subform. Something is very very odd here.
-- 

             John W. Vinson [MVP]

On 27/03/2010 6:12 AM, Dirk Goldgar wrote:
> "Avid Fan" <me@privacy.net> wrote in message
> news:%236Q8lRRzKHA.3884@TK2MSFTNGP06.phx.gbl...
>> I have set a relationship between the master and child tables.
>>
>> Customer.ID -> callnotes.Customer_ID
>>
>> Enforce referential integrity.
>> Cascade updates.
>>
>>
>> I am told Access is supposed to put the customer.ID value in the
>> callnotes.Customer_ID it is not working
>>
>>
>> How do I do it manually in VBA code?
>
>
> You don't. This is controlled by the Link Master Fields and Link Child
> Fields properties of the subform control. If you created that
> relationship after you added the subform to the main form, Access would
> not have been able to guess that those were the linking fields, so you
> have to go to the properties of the subform control -- the control on
> the main form that displays the subform -- and enter "ID" in the Link
> Master Fields property and "Customer_ID" in the Link Child Fields
> property (without quotes around the field names).
>

That was it!!!!!!!!!

For some reason the customer.id  was linked to callnotes.id  not 
callnotes.customer_id.


BIG THANK YOU!!!!!!

On 27/03/2010 12:31 PM, John W. Vinson wrote:
> On Sat, 27 Mar 2010 11:48:32 +1100, Avid Fan<me@privacy.net>  wrote:
>
>> I fill data into the memo text box.  I change the one other control left
>>   combobox
>>
>> I even have a save record and move to next record buttons on the forms
>>
>> I have placed a text box control linked to customer_id  the only way I
>> can get the record to save is by placing a valid ID number in the field.
>
> And this is in fact a Subform in a subform control (not a popup form launched
> from code)?
>
> Please post the SQL view of the mainform and the subform's Recorsource
> properties and (again) the Master Link Field and Child Link Field properties
> of the Subform. Something is very very odd here.

The error was in the Master Link Child link property of the subform. 
for some reason they were linked to customer.Id  callnotes.ID  instead 
of callnotes.customer_ID

It was an "ID ten IT " error (ID10T)

On Sat, 27 Mar 2010 13:06:42 +1100, Avid Fan <me@privacy.net> wrote:

>The error was in the Master Link Child link property of the subform. 
>for some reason they were linked to customer.Id  callnotes.ID  instead 
>of callnotes.customer_ID
>
>It was an "ID ten IT " error (ID10T)

I *won't* post how many times I've been burned by the same kind of error...
<g>

Glad you figured it out!
-- 

             John W. Vinson [MVP]

Dirk, I hope you can help me with a similar problem.
I have a form and subform with the master/child links as such:
Link Master Field=Name
Link Child Field=EmpName
My issue is that when I enter through the EmpName field instead of the field 
being populated with the employee's name the name of the form, ContractsIn, 
is entered into this field.
Any idea why?

Thank you in advance for any help.

Lori


"Dirk Goldgar" wrote:

> "Avid Fan" <me@privacy.net> wrote in message 
> news:%236Q8lRRzKHA.3884@TK2MSFTNGP06.phx.gbl...
> >I have set a relationship between the master and child tables.
> >
> > Customer.ID -> callnotes.Customer_ID
> >
> > Enforce referential integrity.
> > Cascade updates.
> >
> >
> > I am told Access is supposed to put  the customer.ID  value in the 
> > callnotes.Customer_ID  it is not working
> >
> >
> > How do I do it manually in VBA code?
> 
> 
> You don't.  This is controlled by the Link Master Fields and Link Child 
> Fields properties of the subform control.  If you created that relationship 
> after you added the subform to the main form, Access would not have been 
> able to guess that those were the linking fields, so you have to go to the 
> properties of the subform control -- the control on the main form that 
> displays the subform -- and enter "ID" in the Link Master Fields property 
> and "Customer_ID" in the Link Child Fields property (without quotes around 
> the field names).
> 
> -- 
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
> 
> (please reply to the newsgroup)
> 
> .
> 

"SSi308" <SSi308@discussions.microsoft.com> wrote in message 
news:EF4E4E98-1938-411B-83E9-67EB369647AF@microsoft.com...
> Dirk, I hope you can help me with a similar problem.
> I have a form and subform with the master/child links as such:
> Link Master Field=Name
> Link Child Field=EmpName
> My issue is that when I enter through the EmpName field instead of the 
> field
> being populated with the employee's name the name of the form, 
> ContractsIn,
> is entered into this field.
> Any idea why?
>
> Thank you in advance for any help.


I suspect it's because "Name" is a very bad choice for the name of a field, 
since most objects -- including your form! -- have a Name property.  If it's 
not too big a chore, I recommend you change the name of the field "Name" to 
something more specific (such as "EmpName" or "ContactName") in the table 
that holds it and in every query, form, and report that refers to it.

If you don't feel you can do that, then change the name of the *control* on 
the main form that is bound to the Name field.  Change it to something like 
"txtName".  Then put the control name in the Link Master Field property. 
That should work.

Although the above quick fix ought to work, you really ought to do the more 
involved job of changing the name of the field, because using this reserved 
word for a field name will probably cause you a lot more trouble down the 
road.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On Mon, 17 May 2010 13:43:01 -0700, SSi308 <SSi308@discussions.microsoft.com>
wrote:

>Dirk, I hope you can help me with a similar problem.
>I have a form and subform with the master/child links as such:
>Link Master Field=Name
>Link Child Field=EmpName
>My issue is that when I enter through the EmpName field instead of the field 
>being populated with the employee's name the name of the form, ContractsIn, 
>is entered into this field.
>Any idea why?


Because Name is a reserved word. When you refer to Name in a form property,
Access quite reasonably thinks you mean the *name of the form*.

You really should change the <ahem> Name of the Name field to EmpName, or
PersonName, or some other non-reserved word. If you're unable to do so, set
the Link Master Field property to [Name], with the brackets, so Access will
have a better chance of guessing correctly.
-- 

             John W. Vinson [MVP]

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:jpb3v5pj21gl8k4cdokc6a7nnbhvj5pabo@4ax.com...

> You really should change the <ahem> Name of the Name field to EmpName, or
> PersonName, or some other non-reserved word. If you're unable to do so, 
> set
> the Link Master Field property to [Name], with the brackets, so Access 
> will
> have a better chance of guessing correctly.


As a test, John, I tried that and it didn't work.  Using Access 2003, I 
created a table containing a field named "Name", and created a form based on 
that table with a subform linked by the Link Master Field [Name].  It didn't 
seem to matter whether I set the property to "Name" or "[Name]" -- the same 
phenomenon was manifested.  Changing the control name to something else 
fixed the problem.

-- 
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

On Mon, 17 May 2010 17:19:56 -0400, "Dirk Goldgar"
<dg@NOdataSPAMgnostics.com.invalid> wrote:

>"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
>news:jpb3v5pj21gl8k4cdokc6a7nnbhvj5pabo@4ax.com...
>
>> You really should change the <ahem> Name of the Name field to EmpName, or
>> PersonName, or some other non-reserved word. If you're unable to do so, 
>> set
>> the Link Master Field property to [Name], with the brackets, so Access 
>> will
>> have a better chance of guessing correctly.
>
>
>As a test, John, I tried that and it didn't work.  Using Access 2003, I 
>created a table containing a field named "Name", and created a form based on 
>that table with a subform linked by the Link Master Field [Name].  It didn't 
>seem to matter whether I set the property to "Name" or "[Name]" -- the same 
>phenomenon was manifested.  Changing the control name to something else 
>fixed the problem.

Thanks Dirk! I didn't test it (obviously)... another REALLY good reason not to
use reserved words. I'm wondering if this is perhaps something we should
report as a bug; if you in fact cannot use Name, it should let you try in the
first place!
-- 

             John W. Vinson [MVP]