L
Ladybird
How to assign the Primary Key control in a form to another bound control on
the same form?
the same form?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
S
Steve Schapel
Ladybird,
You mean you want two controls on the same form to be bound to the
primary key field in the form's underlying table? Well, this unusual,
but it is just a matter of setting the Control Source property of both
controls to the name of the field.
You mean you want two controls on the same form to be bound to the
primary key field in the form's underlying table? Well, this unusual,
but it is just a matter of setting the Control Source property of both
controls to the name of the field.
L
Ladybird
Yes I did mean the underlying table's PK field.
Tell me why this is unusual as there are lots of my forms like this. It's
how I have built my related fields; where there is a foreign key to link the
tables. Just to let you know; when I made the form (that has in fact three
tables fields on it) The primary table is the form's Record Source I picked
from the form wizard then selected the other tables fields, there is also a
subform(datasheet view)also but not in relationship.
The record source table's PK field is what the other bound FK's controls
reference for the relationships to work.
The thing I am having trouble with is where the FK are bound in this way, I
do not want to have type in the PK number into the FK field's...just want it
automatically done by making the FK's = the PK.
Tell me why this is unusual as there are lots of my forms like this. It's
how I have built my related fields; where there is a foreign key to link the
tables. Just to let you know; when I made the form (that has in fact three
tables fields on it) The primary table is the form's Record Source I picked
from the form wizard then selected the other tables fields, there is also a
subform(datasheet view)also but not in relationship.
The record source table's PK field is what the other bound FK's controls
reference for the relationships to work.
The thing I am having trouble with is where the FK are bound in this way, I
do not want to have type in the PK number into the FK field's...just want it
automatically done by making the FK's = the PK.
S
Steve Schapel
Ladybird,
Thanks for the further information.
If you have two tables as you described, with a PK/FK relationship, it
is normally the table on the 'one' side of the relationship that the
main form is bound to, and the table on the 'many' side of the
relationship is represented in a subform. It is then the setting of the
Link Master fields and Link Child Fields properties of the subform that
allows the automatic entry of the main form's PK value to the subform's
FK control.
It is also possible to have a form based on a querry that includes more
than one table. As long as the query is an updateable query, you can
use this form to enter data, but it quickly gets tricky. In this
scenario, unless the main table's PK is an autonumber, you do need to
have the main table's PK and the second table's FK both represented on
the form... maye this is what you are referring to. And then, to create
a new main table record, you enter the number in the main table's PK,
and to create a new record in the second table, you enter the value of
the main table's PK value for the related record into the control for
the second table's FK. I have never done this myself, but I know it is
possible.
I am not sure whether these comments relate to your question. If not,
maybe you could post back with more explicit details about the form, the
controls on it, and the table(s) involved, so we can talk specifics.
Thanks for the further information.
If you have two tables as you described, with a PK/FK relationship, it
is normally the table on the 'one' side of the relationship that the
main form is bound to, and the table on the 'many' side of the
relationship is represented in a subform. It is then the setting of the
Link Master fields and Link Child Fields properties of the subform that
allows the automatic entry of the main form's PK value to the subform's
FK control.
It is also possible to have a form based on a querry that includes more
than one table. As long as the query is an updateable query, you can
use this form to enter data, but it quickly gets tricky. In this
scenario, unless the main table's PK is an autonumber, you do need to
have the main table's PK and the second table's FK both represented on
the form... maye this is what you are referring to. And then, to create
a new main table record, you enter the number in the main table's PK,
and to create a new record in the second table, you enter the value of
the main table's PK value for the related record into the control for
the second table's FK. I have never done this myself, but I know it is
possible.
I am not sure whether these comments relate to your question. If not,
maybe you could post back with more explicit details about the form, the
controls on it, and the table(s) involved, so we can talk specifics.
L
Ladybird
Many thanks...
I will have to examine why the automatic filling of the fields is not
happening, there is no Link Master field with Child field in the forms
properties box. Not sure if this is because the relationship is not
referential integrity enforced, (actually two tables are the other is not).
I will have to examine why the automatic filling of the fields is not
happening, there is no Link Master field with Child field in the forms
properties box. Not sure if this is because the relationship is not
referential integrity enforced, (actually two tables are the other is not).
L
Ladybird
Yes I'll give more details...
Table 1 is a Customer Table with a customer number PK,
Table 2 is an Customer Identification details form with a customer number as
its FK. these relationships are one(customer) to many(Ident's)integrity
enforced as all customers have more than one type of identification types,
and a customer must have one or more types of id.
Table 3 is (CustOrg)is a variation of the Customer table where the customer
is from a business and records business name and address, and has Customer
PK in its FK field, and is a one to one relationship not enforced as not all
customers come from a business and will not be needed to be filled out.
So I'll re make the form to be a Master form (record source=Customer table)
and all other tables to be subforms, as only the CustOrg is at moment.
Table 1 is a Customer Table with a customer number PK,
Table 2 is an Customer Identification details form with a customer number as
its FK. these relationships are one(customer) to many(Ident's)integrity
enforced as all customers have more than one type of identification types,
and a customer must have one or more types of id.
Table 3 is (CustOrg)is a variation of the Customer table where the customer
is from a business and records business name and address, and has Customer
PK in its FK field, and is a one to one relationship not enforced as not all
customers come from a business and will not be needed to be filled out.
So I'll re make the form to be a Master form (record source=Customer table)
and all other tables to be subforms, as only the CustOrg is at moment.
S
Steve Schapel
Ladybird,
When you create a subform, the subform's Link Master Fields and Link
Child Fields properties are automatically entered for you by Access, and
sometimes they are not. There are a number of factors that determine
whether this happens or not, but referential integrity enforced in a
relationship is not one of them. If they are not entered correctly for
you, it is simply a matter of entering the name of the field that is the
basis of the relationship between the main form and the subform... this
is usually (but not always) the primary key field of the table that the
main form is based on, and the corresponding foreign key field in the
subform's record source.
When you create a subform, the subform's Link Master Fields and Link
Child Fields properties are automatically entered for you by Access, and
sometimes they are not. There are a number of factors that determine
whether this happens or not, but referential integrity enforced in a
relationship is not one of them. If they are not entered correctly for
you, it is simply a matter of entering the name of the field that is the
basis of the relationship between the main form and the subform... this
is usually (but not always) the primary key field of the table that the
main form is based on, and the corresponding foreign key field in the
subform's record source.
S
Steve Schapel
Ladybird,
Yes, I would definitely make a Customer Identification form, and place
it as a subform on the Customer form.
On the other hand, since the relationship between Customer and CustOrg
is one-to-one, it is probably not applicable to have a subform. Not
only that, but the relationship between the two tables should be on the
primary key from each, not a PK/FK arrangement. In this case, it is
probably better to combine these tables into one. If not, you can make
a query that includes both tables, suitably joined, and base the main
form on this query.
Yes, I would definitely make a Customer Identification form, and place
it as a subform on the Customer form.
On the other hand, since the relationship between Customer and CustOrg
is one-to-one, it is probably not applicable to have a subform. Not
only that, but the relationship between the two tables should be on the
primary key from each, not a PK/FK arrangement. In this case, it is
probably better to combine these tables into one. If not, you can make
a query that includes both tables, suitably joined, and base the main
form on this query.
L
Ladybird
That fills in some gaps in my knowledge, certainly clears up the problem
with my forms.
I am surprised to see no Link master with Child field in my form...it seems
the way I put all the fields in through 'form wizard' disabled it somehow.
In my other forms. that do not rely directly on a related fields, but are
later related in the final forms (using an intermediate 'temp form' for
this) and need the PK field to be entered into the FK filed, I have found by
using the Default Value =[my field] seems to work...untested at this
stage...but does work for other bound controls. Which is good as there are
many instances of this in my database.
Well I'm finding all this table/relationship/forms/queries more an art-form
and a circus juggling act all in one!
Thanks for clearing this up Steve.
with my forms.
I am surprised to see no Link master with Child field in my form...it seems
the way I put all the fields in through 'form wizard' disabled it somehow.
In my other forms. that do not rely directly on a related fields, but are
later related in the final forms (using an intermediate 'temp form' for
this) and need the PK field to be entered into the FK filed, I have found by
using the Default Value =[my field] seems to work...untested at this
stage...but does work for other bound controls. Which is good as there are
many instances of this in my database.
Well I'm finding all this table/relationship/forms/queries more an art-form
and a circus juggling act all in one!
Thanks for clearing this up Steve.