Form updating keyfields

  • Thread starter Thread starter Steve Marsden
  • Start date Start date
S

Steve Marsden

Further to my earlier post I have simplified the problem with a simple
database setup which explains it more clearly I hope.

Using Access 2002, I have created a database with three tables, main,join1
and join2.

Main

ID Autonumber Primary key
text Text

Join1
ID Long Integer Primary Key
text Text

Join2
ID Long Integer Primary Key
text Text

There is a one to one relationship with a left join defined between ID on
main and ID on Join1 and also on ID on main and ID on join2.

I have used the form wizard to create a form which displays all fields from
all tables. This is the query it has generated.

SELECT Main.ID AS Main_ID, Main.text AS Main_text, join1.ID AS join1_ID,
join1.text AS join1_text, join2.ID AS join2_ID, join2.text AS join2_text
FROM (Main LEFT JOIN join1 ON Main.ID = join1.ID) LEFT JOIN join2 ON Main.ID
= join2.ID;

The problem is this:

If you use the form to add a record and enter values in the three text
fields all is OK and an ID is generated for the main table and used for the
ID for join1 and join2.

If you add a record and only enter data into main.text, a record is added to
main but not to join1 or join2 which is OK. If you now go back to that
record and enter text into join1.text and tab off it, the correct ID from
main.ID is entered into join1.ID and a record is written correctly to join1.

BUT if you now go back to that record again and enter data into join2.text
and tab off it, the main.ID is correctly put into join2.ID BUT join1.ID gets
changed to 0 by ACCESS!!!. This obviously can cause a problem as if this is
written away the ID field of the join1 table for this record is changed to 0
which either fails if referential integrity is on (as there isn't an ID of 0
in the main table) modifies the record's ID field incorrectly if
referential integrity is off.

Can anyone advise on what's going on and how to avoid it.

Thanks
Steve
 
Thanks to help from Tina I have solved this. It is caused by Access setting
the default value field a number field to 0. It does this automatically and
this default of 0 was what was setting my ID fields to 0

Thanks again Tina
 
Back
Top