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
|
|||
|
|||
'Cannot Add' Error Message - Forms
Hi there,
I have been working on a database for my golf club that records then prints prize vouchers for our competitions. I have an "Event Table", primary key of EventID in which all the competition details are entered. The "Event Table" has a 'VoucherID' foreign key field which gives a one-to-many relationship with the "Voucher Table". That is, for any Event you can have more than one prize voucher. The "Event" form is based on a query of the "Event Table" and has two combo dropdown boxes that allow you to select 'Event Type' and the 'Employee'. I have not- in-list procedures for these combo boxes and they work fine. The "Voucher" form, primary key of VoucherID, is linked to the 'Event' form as a subform, the many side of the above relationship. The "Voucher Table" has four foreign key fields of which the "Member" and "Vistor" fields are giving me a problem. Again the 'Voucher' form is based on a query of the "Voucher Table" and related fields in the four foreign key fields/tables. Two of the foreign field key present no problem as the have attached combo boxes and are looking at only one piece of information from their tables. My problem is that when I set up the voucher form and create combo drop down boxes for the "Member" or "Visitor" (which fills in related information on the form) I have to choose a name from both combo boxes for the record to be saved and printed as a voucher. If I leave the "Visitor" combo box blank I get the following error message 'You cannot add or change a record because a related record is required in table Visitors' and vice a versa for the 'Member' combo box. Is there away around this problem? The way I originally wanted to do the form was to have an option group on the voucher form that enabled/disabled either the 'Member' combo box or the 'Visitor' combo box depending on the choice made in the option group but this approach doesn't work. If the problem is because I have the two foreign keys in the "Voucher Table" should I create another table for Visitors Vouchers only and have this linked to Event Table as the same way as above and thus have two subforms on the Event form. Can you have two subforms related to the Event form by the EventID primary field? If anyone has any suggestions on how to best overcome this problem or what I am doing wrong in the first place I would like to thank you in advance for your reply. Regards Allan March |
#2
|
|||
|
|||
Just a couple of questions to clarify the question:
1. In what table(s) are the Member & Visitor the primary keys? 2. Is there a) always, b) ever, or c) sometimes both a Member & Visitor on a voucher? If a voucher has only a member OR a visitor, perhaps you need a table structure in which the Vouchers table has a Member Yes/No box. If it is True, then the info will be for a member; if False, then for a visitor. In the Click event of this box, you could set the RowSource of the person's name to either show Members or Visitors. "Allan March" wrote: Hi there, I have been working on a database for my golf club that records then prints prize vouchers for our competitions. I have an "Event Table", primary key of EventID in which all the competition details are entered. The "Event Table" has a 'VoucherID' foreign key field which gives a one-to-many relationship with the "Voucher Table". That is, for any Event you can have more than one prize voucher. The "Event" form is based on a query of the "Event Table" and has two combo dropdown boxes that allow you to select 'Event Type' and the 'Employee'. I have not- in-list procedures for these combo boxes and they work fine. The "Voucher" form, primary key of VoucherID, is linked to the 'Event' form as a subform, the many side of the above relationship. The "Voucher Table" has four foreign key fields of which the "Member" and "Vistor" fields are giving me a problem. Again the 'Voucher' form is based on a query of the "Voucher Table" and related fields in the four foreign key fields/tables. Two of the foreign field key present no problem as the have attached combo boxes and are looking at only one piece of information from their tables. My problem is that when I set up the voucher form and create combo drop down boxes for the "Member" or "Visitor" (which fills in related information on the form) I have to choose a name from both combo boxes for the record to be saved and printed as a voucher. If I leave the "Visitor" combo box blank I get the following error message 'You cannot add or change a record because a related record is required in table Visitors' and vice a versa for the 'Member' combo box. Is there away around this problem? The way I originally wanted to do the form was to have an option group on the voucher form that enabled/disabled either the 'Member' combo box or the 'Visitor' combo box depending on the choice made in the option group but this approach doesn't work. If the problem is because I have the two foreign keys in the "Voucher Table" should I create another table for Visitors Vouchers only and have this linked to Event Table as the same way as above and thus have two subforms on the Event form. Can you have two subforms related to the Event form by the EventID primary field? If anyone has any suggestions on how to best overcome this problem or what I am doing wrong in the first place I would like to thank you in advance for your reply. Regards Allan March |
#3
|
|||
|
|||
Thanks for your reply Brian.
To answer your questions: 1) The MemberID is part of the Members Table and and the VisitorId is part of the Visitor Table. Both are in a one to many relationship with the voucher table. That is, a Member or Visitor can win many different Vouchers. 2) There can only be either a Member on a single voucher or a Visitor on a single voucher not both. 3) The Voucher form is a subform of the Event form and is linked in Parent/Child relatonship via the EventID field. Your suggestion is a good one but I am not sure how I would go about doing it as the information I need to put on the voucher varies from a member to a vistor e.g I don't need to put the Members golf club on it as, of course, being a member we know his club but I need to put a visitor's club on the voucher. I don't think I could work out how to make it autofill the details from the Visitor Table this way. Your suggestions are most welcome. Perhaps my whole approach to the problem is wrong. Perhaps I should have the voucher as the parent and the event as the child in a one to one relationship. Anyway as I said your thoughts are most welcome. Regards Allan March "Brian" wrote: Just a couple of questions to clarify the question: 1. In what table(s) are the Member & Visitor the primary keys? 2. Is there a) always, b) ever, or c) sometimes both a Member & Visitor on a voucher? If a voucher has only a member OR a visitor, perhaps you need a table structure in which the Vouchers table has a Member Yes/No box. If it is True, then the info will be for a member; if False, then for a visitor. In the Click event of this box, you could set the RowSource of the person's name to either show Members or Visitors. "Allan March" wrote: Hi there, I have been working on a database for my golf club that records then prints prize vouchers for our competitions. I have an "Event Table", primary key of EventID in which all the competition details are entered. The "Event Table" has a 'VoucherID' foreign key field which gives a one-to-many relationship with the "Voucher Table". That is, for any Event you can have more than one prize voucher. The "Event" form is based on a query of the "Event Table" and has two combo dropdown boxes that allow you to select 'Event Type' and the 'Employee'. I have not- in-list procedures for these combo boxes and they work fine. The "Voucher" form, primary key of VoucherID, is linked to the 'Event' form as a subform, the many side of the above relationship. The "Voucher Table" has four foreign key fields of which the "Member" and "Vistor" fields are giving me a problem. Again the 'Voucher' form is based on a query of the "Voucher Table" and related fields in the four foreign key fields/tables. Two of the foreign field key present no problem as the have attached combo boxes and are looking at only one piece of information from their tables. My problem is that when I set up the voucher form and create combo drop down boxes for the "Member" or "Visitor" (which fills in related information on the form) I have to choose a name from both combo boxes for the record to be saved and printed as a voucher. If I leave the "Visitor" combo box blank I get the following error message 'You cannot add or change a record because a related record is required in table Visitors' and vice a versa for the 'Member' combo box. Is there away around this problem? The way I originally wanted to do the form was to have an option group on the voucher form that enabled/disabled either the 'Member' combo box or the 'Visitor' combo box depending on the choice made in the option group but this approach doesn't work. If the problem is because I have the two foreign keys in the "Voucher Table" should I create another table for Visitors Vouchers only and have this linked to Event Table as the same way as above and thus have two subforms on the Event form. Can you have two subforms related to the Event form by the EventID primary field? If anyone has any suggestions on how to best overcome this problem or what I am doing wrong in the first place I would like to thank you in advance for your reply. Regards Allan March |
#4
|
|||
|
|||
If there are several or many fields in common (i.e. used whether it is a
member or a visitor), I would probably them into one table, and a Yes/No Member field, and just leave some fields blank in the table if it is a visitor, and others if it is a member. You can control this on the form where you input the personal info; just enable/disable the various boxes in the Click event of the Member box, based on whether the individual is a member or a visitor. Then, once you select the individual on the Vouchers form (AfterUpdate of the selection combo box), use: If DLookup("[Member]","[PeopleTable]","[PersonID] = " & Me.[PersonSelector]) Then 'enable & populate boxes relevant to members; disable others Else 'enable & populate boxes relevant to visitors;disable others End if However, enough on my design philosoph, and back to the original issue (to work within your existing design). The message you are getting normally indicates that there is an entry in the control on the form, but there is no corresponding entry in the foreign table. Check these two things: 1. Are both Visitor & Member are required in the Vouchers table, but you really want to require only one or the other. If this is the case, remove the required property from the table and add some code on From_BeforeUpdate to ensure that there is a member OR a visitor, not both, or 2. When you select a member, does Visitor also have a value (and vice versa)? Throw a "MsgBox VisitorID" and "MsgBox VisitorID" into your VBA code (perhaps Form_BeforeUpdate?) to see if it pops up values for both. "Allan March" wrote: Thanks for your reply Brian. To answer your questions: 1) The MemberID is part of the Members Table and and the VisitorId is part of the Visitor Table. Both are in a one to many relationship with the voucher table. That is, a Member or Visitor can win many different Vouchers. 2) There can only be either a Member on a single voucher or a Visitor on a single voucher not both. 3) The Voucher form is a subform of the Event form and is linked in Parent/Child relatonship via the EventID field. Your suggestion is a good one but I am not sure how I would go about doing it as the information I need to put on the voucher varies from a member to a vistor e.g I don't need to put the Members golf club on it as, of course, being a member we know his club but I need to put a visitor's club on the voucher. I don't think I could work out how to make it autofill the details from the Visitor Table this way. Your suggestions are most welcome. Perhaps my whole approach to the problem is wrong. Perhaps I should have the voucher as the parent and the event as the child in a one to one relationship. Anyway as I said your thoughts are most welcome. Regards Allan March "Brian" wrote: Just a couple of questions to clarify the question: 1. In what table(s) are the Member & Visitor the primary keys? 2. Is there a) always, b) ever, or c) sometimes both a Member & Visitor on a voucher? If a voucher has only a member OR a visitor, perhaps you need a table structure in which the Vouchers table has a Member Yes/No box. If it is True, then the info will be for a member; if False, then for a visitor. In the Click event of this box, you could set the RowSource of the person's name to either show Members or Visitors. "Allan March" wrote: Hi there, I have been working on a database for my golf club that records then prints prize vouchers for our competitions. I have an "Event Table", primary key of EventID in which all the competition details are entered. The "Event Table" has a 'VoucherID' foreign key field which gives a one-to-many relationship with the "Voucher Table". That is, for any Event you can have more than one prize voucher. The "Event" form is based on a query of the "Event Table" and has two combo dropdown boxes that allow you to select 'Event Type' and the 'Employee'. I have not- in-list procedures for these combo boxes and they work fine. The "Voucher" form, primary key of VoucherID, is linked to the 'Event' form as a subform, the many side of the above relationship. The "Voucher Table" has four foreign key fields of which the "Member" and "Vistor" fields are giving me a problem. Again the 'Voucher' form is based on a query of the "Voucher Table" and related fields in the four foreign key fields/tables. Two of the foreign field key present no problem as the have attached combo boxes and are looking at only one piece of information from their tables. My problem is that when I set up the voucher form and create combo drop down boxes for the "Member" or "Visitor" (which fills in related information on the form) I have to choose a name from both combo boxes for the record to be saved and printed as a voucher. If I leave the "Visitor" combo box blank I get the following error message 'You cannot add or change a record because a related record is required in table Visitors' and vice a versa for the 'Member' combo box. Is there away around this problem? The way I originally wanted to do the form was to have an option group on the voucher form that enabled/disabled either the 'Member' combo box or the 'Visitor' combo box depending on the choice made in the option group but this approach doesn't work. If the problem is because I have the two foreign keys in the "Voucher Table" should I create another table for Visitors Vouchers only and have this linked to Event Table as the same way as above and thus have two subforms on the Event form. Can you have two subforms related to the Event form by the EventID primary field? If anyone has any suggestions on how to best overcome this problem or what I am doing wrong in the first place I would like to thank you in advance for your reply. Regards Allan March |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Problems with custom forms not displaying, (using outlook form ins | Katherines01 | Contacts | 1 | January 13th, 2005 02:25 PM |
Forms Will Not Save | accessquestion | General Discussion | 1 | January 12th, 2005 08:28 AM |
General question about naming conventions for forms | Mister John Doe | Using Forms | 1 | January 6th, 2005 01:31 AM |
Menubar listing open forms? | Harmannus | Using Forms | 0 | December 23rd, 2004 12:33 AM |
Advantages of unbound forms? | Rob Rutherford | Using Forms | 2 | December 9th, 2004 07:48 AM |