ID number getting overwritten

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Elena Kuo - 22 Sep 2005 01:35 GMT

I have two tables a main table and another we'll call Table 2. I joined them
so it says "all records from main table and only records from Table 2 when
they are equal, and matched by ID. There are no records yet in Table 2.
When I make a query or form and go to add information into Table 2, it
overwrites the ID from the maintable with a 0, instead of starting a record
with the ID From the main table as it's ID number.

I have set up these tables before but my expertise is very minimal so I
could have easily set it up correctly by mistake previous times... the only
difference I can see between my other databases and this is that the join
type says one to one on the bad one and it says one to many on the working
database. But I can;'t figure out how to set it to be a 1 to many join.

Reply to this Message

Allen Browne - 22 Sep 2005 04:40 GMT

Open Table2 in design view.
Select the foreign key field (the one that you match to Table 1.)
In the lower pane, remove the zero from the field's Default Value.

While you are there, consider setting the Required property for this field
to Yes, so you don't get orphaned records in Table 2 (i.e. records where the
foreign key is null.)

Signature

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.

>I have two tables a main table and another we'll call Table 2. I joined
>them
[quoted text clipped - 11 lines]
> type says one to one on the bad one and it says one to many on the working
> database. But I can;'t figure out how to set it to be a 1 to many join.

Reply to this Message

John Vinson - 22 Sep 2005 04:49 GMT

>I have two tables a main table and another we'll call Table 2. I joined them
>so it says "all records from main table and only records from Table 2 when
>they are equal, and matched by ID. There are no records yet in Table 2.
>When I make a query or form and go to add information into Table 2, it
>overwrites the ID from the maintable with a 0, instead of starting a record
>with the ID From the main table as it's ID number.

It's probably just that you have the ID from the main table bound to
the Form, rather than the child table. I would suggest a different
approach: rather than a Query joining the two tables, use a Form based
on Table1 with a Subform based on Table2. Make ID the Master Link
Field and Child Link Field; this will let you enter multiple records
in Table2 for each record in Table1, and keep the two ID's in synch.

John W. Vinson[MVP]

Reply to this Message