Ralph, I'm a bit confused here.
If you use the primary key of tableB as a foreign key to tableA, you cannot
have a one-to-many relationship between TableA and TableB. Since TableB.ID
is unqiue, you cannot have a 2nd record with the same number and therefore
you cannot have many related records in TableB.
Keep ID (the autonumber) as the primary key in TableB, but add another field
to be the foreign key.
Give it a Data Type of Number.
In the lower pane, leave the Field Size as Long Integer
Delete the zero from the Default Value property.
Set the Required property to Yes.
Save.
Now open the relationship window (Tools menu), and create a relationship
between this new field and TableA.AffNo.
Open your main form in design view.
Right-click the edge of the subform control, and choose Properties.
Beside the LinkChildFields property (Data tab of the Properties box), enter
the name of your new field.
Access will now fill in this value for you automatically whenever you enter
a new value in the subform (assuming the main form is not at a new record).
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Ralph Birden" <ra**********@s mc.etat.lu> wrote in message
news:40******** @news.vo.lu...
Here's my situation:
I have 2 tables, let's call them TableA and TableB. TableA has a primary
key called AffNo, TableB's primary key (--> foreign key in TableA) is called
ID, and is Autonumber.
I want to do a form to input information for TableA, with a subform for
TableB where i can enter several records concerning TableA. This seems to
work, however, whenever i click on the 1st Field in the TableB subform, it
generates my Autonumber, but it also generates the record n+1, with empty
fields with exception of Autonumber, wich then has the same number in it
as the record n. This of course triggers a relationship problem because of
the uniqueness of TableB's primary key.
I would be grateful for any suggestions concerning this problem
Thank you,
Ralph Birden