A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Unable to add new records to Form



 
 
Thread Tools Display Modes
  #1  
Old June 5th, 2005, 09:40 AM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default Unable to add new records to Form

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.
--
2nd_Stage_User
  #2  
Old June 5th, 2005, 09:57 AM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default

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.
--
2nd_Stage_User


"2nd_Stage_User" wrote:

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.
--
2nd_Stage_User

  #3  
Old June 5th, 2005, 03:38 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


  #4  
Old June 5th, 2005, 09:44 PM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default

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


"Rick Brandt" wrote:

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



  #5  
Old June 5th, 2005, 09:47 PM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default

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


"2nd_Stage_User" wrote:

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.
--
2nd_Stage_User

  #6  
Old June 5th, 2005, 09:53 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


  #7  
Old June 5th, 2005, 11:17 PM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default

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 Brandt" wrote:

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



  #8  
Old June 5th, 2005, 11:51 PM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default

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 Brandt" wrote:

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



  #9  
Old June 6th, 2005, 12:00 AM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


  #10  
Old June 6th, 2005, 12:25 AM
2nd_Stage_User
external usenet poster
 
Posts: n/a
Default

Thanks for all your time and help!
--
2nd_Stage_User


"Rick Brandt" wrote:

2nd_Stage_User wrote:
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.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Design help, please SillySally Using Forms 27 March 6th, 2005 05:11 AM
Need query to separate 2 entry types in a table field Jan Il Running & Setting Up Queries 31 November 23rd, 2004 06:57 PM
Open another form based on the same records as in the first form Anders Berlin New Users 1 October 1st, 2004 12:40 AM
Open another form based on the same records as in the first form Anders Berlin Using Forms 1 September 30th, 2004 09:57 AM
surely a form with a ListBox can be used in a query? 1.156 Running & Setting Up Queries 14 June 2nd, 2004 04:54 PM


All times are GMT +1. The time now is 10:34 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.