Limking form and subform with no common field?

[Tony Williams]

I have a table with first name, surname and other fields. I have another
table which holds details of ID cards that have been issued. However there
isn't a common field in both tables that I can join them on. I can't use
surname, how many Smith's are in the world? I want to create a form that is
based on my first table but have a sub form in datasheet format that shows
the history of id cards issued to that person. How do I link the tables and
therefore the main form and subform when I haven't got a common field?
Thanks
Tony

 

[ansentry via AccessMonster.com]

I have a table with first name, surname and other fields. I have another
table which holds details of ID cards that have been issued. However there
isn't a common field in both tables that I can join them on. I can't use
surname, how many Smith's are in the world? I want to create a form that is
based on my first table but have a sub form in datasheet format that shows
the history of id cards issued to that person. How do I link the tables and
therefore the main form and subform when I haven't got a common field?
Thanks
Tony

In your table that holds the names etc what are you using as your primary key
and what is its type (Auto Number etc)?
In your second table what are you using for the primary key and what is its
type (Auto Number etc)

 

[Arvin Meyer [MVP]]

You need a primary key in the main table and a foreign key in the ID details
table. The values are unique for the primary key, and can be duplicates in
the sub table. Have a look at the Northwind sample database that came with
your copy of Access for an example.

 

[Tony Williams]

Thanks Arvin

You need a primary key in the main table and a foreign key in the ID
details table. The values are unique for the primary key, and can be
duplicates in the sub table. Have a look at the Northwind sample database
that came with your copy of Access for an example.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Tony Williams]

The primary keys are the Access generated keys
That help?
Tony

 

[Arvin Meyer [MVP]]

The Access generated keys are Autonumbers in the main table. You need to
have the same key as a foreign key in the sub table and join (link) the
primary to the foreign key as either a relationship (usually preferable) or
a query join. In forms/subforms, they are the same as the
LinkMaster/LinkChild controls listed in the subform's property sheet data
tab.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

The primary keys are the Access generated keys
That help?
Tony

 

[Tony Williams]

Thanks again Arvin I've worked it out now.
Regards
Tony

The Access generated keys are Autonumbers in the main table. You need to
have the same key as a foreign key in the sub table and join (link) the
primary to the foreign key as either a relationship (usually preferable)
or a query join. In forms/subforms, they are the same as the
LinkMaster/LinkChild controls listed in the subform's property sheet data
tab.