Unable to add new records to Form

[Guest]

I am unable to add new records to Form I created.

The Form was created from the fields of 4 tables, using the Form Wizard.
After completing data entry for first record, and then clicking "add a new
record" button, I get a "You cannot add or change a record because a related
record is required in table 'Main'" pop-up message.

I've done sufficient testing of the "problem Form" to see exactly what's
causing the problem, but I don't know how to fix what I see. Before telling
exactly what I see, I'll provide brief description of pertinent details of
database.

The database consists of 4 tables: Main, Details, Author, and Subject. The
primary key of the Main table is the foreign key in each of the 3 other
tables. In the Main table, the primary key (PageID) is assigned the
"Autonumber" data type (Long Integer). In the other 3 tables, the foreign
key (PageID) is assigned the "Number" data type (Long Integer).

Now, on to exactly what I see. As I complete data entry for fields from 2nd
table, and begin data entry for fields from 3rd table, the value of foreign
key field from 2nd table switches from correct value to value of "0" (zero).
(The "correct value" of foreign key field from 2nd table was "automatically
generated," after completing data entry for fields from 1st table, and during
data entry for fields from 2nd table.)

Likewise, as I complete data entry for fields from 3rd table, and begin data
entry for fields from 4th table, the value of foreign key field from 3rd
table also switches from correct value to value of "0" (zero). (The "correct
value" of foreign key field from 3rd table was "automatically generated,"
after completing data entry for fields from 2nd table, and during data entry
for fields from 3rd table.)

Any ideas why the "4-table" Form generates the "switching values?"

When I create a Form utilizing only 2 of the 4 tables, I am able to
successfully add new records.

 

[Guest]

Why is "line formatting" on my original question so bad?

I originally typed it in Notepad, and then copied and pasted it into Post
Discussion window. The message looked perfect in Post Discussion window.

This is my first posting. Please, forgive ugly line formatting and tell me
how to avoid this. I'd be glad to resubmit question after learning how to
clean-up line formatting.

Thanks.

 

[Rick Brandt]

I am unable to add new records to Form I created.

The Form was created from the fields of 4 tables, using the Form

[snip]

Common problem/question.

Multi-table queries often produce result sets that are read-only. There is a
help topic "When can I update data in a query?" that explains the situation.

While some multi-table queries are still editable and ones that are not can
often be tweaked so that they are, I don't recommend it as a general practice.
Multi-table queiries are better suited for reports or other data-read processes.
For forms that require editing the data you are much better off using a form
with subforms each being bound to a single table.

 

[Guest]

Rick,

Thank you for your reply! My problem didn't involve "queries" (multi-table,
or otherwise), so I wasn't able to relate the multi-table query info to my
problem. Nonetheless, I respect that you did relate this to my problem, so I
will look at the "Help" topic you referenced. The last sentence in your
answer, about Forms, is helpful. Thank you. Minus a direct answer to my
specific problem, your Form info provides me with an alternative.
--
2nd_Stage_User

I am unable to add new records to Form I created.

The Form was created from the fields of 4 tables, using the Form

[snip]

Common problem/question.

Multi-table queries often produce result sets that are read-only. There is a
help topic "When can I update data in a query?" that explains the situation.

While some multi-table queries are still editable and ones that are not can
often be tweaked so that they are, I don't recommend it as a general practice.
Multi-table queiries are better suited for reports or other data-read processes.
For forms that require editing the data you are much better off using a form
with subforms each being bound to a single table.

 

[Guest]

The version I'm using is Access 2002. Sorry, I forgot to note the version in
original posting.

 

[Rick Brandt]

Rick,

Thank you for your reply! My problem didn't involve "queries"
(multi-table, or otherwise), so I wasn't able to relate the
multi-table query info to my problem. [snip]

You said...

"The Form was created from the fields of 4 tables, using the Form Wizard."

If the form was created from the fields of 4 tables then it IS based on a
multi-table query. If you used the form wizard then it just created the query
for you. Look at the RecordSource of the form in design view an dyou will see
it.

 

[Guest]

Thanks for settting me straight on "multi-table query" issue! I expected
that my inability to "relate" to the multi-table part of your original answer
was probably due to my novice background. With your current explanation,
I've learned another piece of the Access puzzle (in addition to the piece
about creating a Form with sub-forms, with each sub-form bound to a single
table). Your help has "unstuck"me, on my current project. Now, I can move
forward. Yea! Thanks! Very appreciated. (Learning about the RecordSource
property of a Form was cool, too!)
--
2nd_Stage_User

Rick,

Thank you for your reply! My problem didn't involve "queries"
(multi-table, or otherwise), so I wasn't able to relate the
multi-table query info to my problem. [snip]

You said...

"The Form was created from the fields of 4 tables, using the Form Wizard."

If the form was created from the fields of 4 tables then it IS based on a
multi-table query. If you used the form wizard then it just created the query
for you. Look at the RecordSource of the form in design view an dyou will see
it.

 

[Guest]

Given that the specific cause for not being able to enter a new record into
the Form was the Foreign Key fields (from tables 2 and 3) switching from
correct value to "0" (zero) value, any ideas why the two Foreign Key values
switched ???

(The value of the Foreign Key field from 2nd table switched from correct
value to zero, when data entry moved out of fields from 2nd table, and into
fields from 3rd table. The value of the Foreign Key field from 3rd table
switched from correct value to zero, when data entry moved out of fields from
3rd table, and into fields from 4th table.)
--
2nd_Stage_User

Rick,

Thank you for your reply! My problem didn't involve "queries"
(multi-table, or otherwise), so I wasn't able to relate the
multi-table query info to my problem. [snip]

You said...

"The Form was created from the fields of 4 tables, using the Form Wizard."

If the form was created from the fields of 4 tables then it IS based on a
multi-table query. If you used the form wizard then it just created the query
for you. Look at the RecordSource of the form in design view an dyou will see
it.

 

[Rick Brandt]

Given that the specific cause for not being able to enter a new
record into the Form was the Foreign Key fields (from tables 2 and 3)
switching from correct value to "0" (zero) value, any ideas why the
two Foreign Key values switched ???

(The value of the Foreign Key field from 2nd table switched from
correct value to zero, when data entry moved out of fields from 2nd
table, and into fields from 3rd table. The value of the Foreign Key
field from 3rd table switched from correct value to zero, when data
entry moved out of fields from 3rd table, and into fields from 4th
table.)

While multi-table queries can often be tweaked such that they will allow edits
it is not an area I am that familiar with as I simply never try it that often.
I stick with forms and subforms each bound to a single table when I want to make
data changes.

 

[Guest]

Thanks for all your time and help!