Updating a joined query from a form

[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

 

[[MVP] S. Clark]

Explore the concept of using subforms rather than the uber-joins that you
have now. The subform paradigm will keep all of the ID's straight for you.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Steve Marsden]

Steve

Also, further to my last comments, if I have a subform with text boxes to
display the one record of data, how can I stop the TAB key moving to the
next record which then displays blank because I've moved to a record which
doesn't match my main ID. (Not sure if I've explained that properly but I
would be trying to make the subform look like text fields on my main form
and would want to TAB between them). Tab on the last field of a subform
seems to move you to the next record which I wouldn't want)

Steve

 

[[MVP] S. Clark]

What is the nature of the data that you're working with?
i.e. Church Donations, Order Invoicing, Baseball Card Collection, Medical

I just can't understand why you need to have two One-to-One relationships.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Steve Marsden]

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