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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |