Data entry problems with form based on Query

[aardvick]

I have 3 tables that are in one-to-one relationship (one parent, 2 children).
I have a form based on a query that joins all three together. My form has
no subforms, but is organized with 3 tabs - one corresponding to each table.

Data entry works fine for new records when at least one field from each tab
is filled in (ie a new record is created for each of the three tables). Data
entry works fine when only fields for the parent table are filled in (a new
record is created for parent table only).

The problem comes when I use the form to add data to the child table LATER -
when the parent record has already been created (as in case 2 above).
Sometimes it works fine and a new record is created in the child table with
the appropriate FK, and sometimes I get the following error:

"You cannot add or change a record because a related record is required in
table 'parent table'."

Hello, there *is* already a parent record there. What is going on?
Many thanks in advance to any who reply!

 

[Ken Snell \(MVP\)]

The best design for your form would be to have the parent table's data be
handled on the first tab, have one of the child table's data handled on the
second tab via a subform, and the other child table's data on the third tab
via a subform. ACCESS handles the insertion of children records very
efficiently and easily with subforms.

The problem that you're seeing probably is because of the relationships that
you have between the tables and that your query does not include all the
linking fields in the query's output list. Withou all the linking fields,
sometimes the query doesn't identify the correct parent or child record.

 

[aardvick]

Ken, Thanks so much for your reply. I really don't want to switch to
subforms if I can help it because I'd have to rewrite so much code. Also,
the form itself is already a sub-form. (Don't know how many forms w/in forms
one can have)

I will try to fix the query first. The PK for the parent table is the FK
for the children. Unfortunately they all have the same name: Staff_ID.
Renaming them alone didn't seem to help. Now I've added these new fields to
the tabs and so far so good.

I guess the other alternative would be to merge all the data into one table
instead of 3 tables (since they are one-to-one anyways). There is no real
reason to keep them apart except that the table will have so many fields...

Thanks a million!
-aardvick

 

[Ken Snell \(MVP\)]

I believe you can have 7 levels of nested subforms, so you probably would
not have any problem with the setup you have if you were to switch to using
subforms on the last two tab pages.

Having one-to-one tables is needed when, if all fields are in one table, you
need more indices than the table allows OR you'd exceed the 255 fields per
table limit (which is a lot of fields!).

Having one-to-one tables is often useful when you have data that may need to
be viewed only by some people; or when you may not always have a value for
the field (then the field can be put in the separate table).