OK my subform is not entering saving the key from the master datasource.

[Avid Fan]

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?

 

[Dirk Goldgar]

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).

 

[John W. Vinson]

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?

 

[Avid Fan]

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.

 

[John W. Vinson]

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.

 

[Avid Fan]

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!!!!!!

 

[Avid Fan]

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)

 

[John W. Vinson]

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!

 

[SSi308]

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]

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.

 

[John W. Vinson]

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.

 

[Dirk Goldgar]

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.

 

[John W. Vinson]

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!