Autonumber problem

[Ralph Birden]

Hi all,

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

[Peter Hoyle]

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

My understanding of this would be that: -.

If TableA has a one to many relationship with TableB then the primary key
from TableA (AffNo) should be a foreign key in TableB.
The problems you mention would then not exist,

Cheers,
Peter

[Ralph Birden]

Hi Peter,

That is right, but TableB has a one-to-many with TableA, that's why TableB
primary becomes foreign in TableA

Ralph Birden

"Peter Hoyle" <pe*********@ntlworld-NOT.com> wrote in message
news:OR0qc.12005$7S2.4595@newsfe1-win...

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.

My understanding of this would be that: -.

If TableA has a one to many relationship with TableB then the primary key
from TableA (AffNo) should be a foreign key in TableB.
The problems you mention would then not exist,

Cheers,
Peter

[Allen Browne]

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**********@smc.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