ID number getting overwritten

[Guest]

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.

 

[Allen Browne]

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

 

[John Vinson]

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]