Prevent Duplicates
When entering data into a subform, particularly when you select The data from a combobox, then you probably don’t want the user to be able to select the same item twice. You may want to count the various entries at some stage and having duplicates would just make this inaccurate. There are numerous reasons for why you would want to prevent duplicates. One way you can prevent duplicates is by applying an index to the table.
Prevent Duplicates
Prevent Duplicates
Video 1 (5:28)
In this particular example the index is applied to two fields. You need to apply the index to two Fields because the combobox in the sub-form has two columns, one column storing the selection and one storing a reference to the the master field in the the parent form. For more information about subforms, parent forms child forms, check out my blogs on sub-forms here:-
Duplicate Error
If you get the following error message from Microsoft "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or Fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." (Text From The Image)… The information on this page about indexes might give you an indication of what the problem is. I directed The OP of this thread:- "Save Error" to this web-page, and I believe the Info here was helpful in solving the Op's problem…
Video 1 (5:28)
YouTube - Index
00:16 demonstration of the current setup which allows the entry of duplicate records in the subform
00:25 normally you want to prevent duplicate entries because it can cause you problems, particularly if you want to count how many — a duplicate would give you a misleading result
00:56 before you attempt to create an index make sure you haven't got any duplicate values, otherwise you will not be allowed to apply the index
01:16 note that without an index you can add duplicate records into the table
01:30 open the table in design view
01:38 then select indexes under the design tab
01:42 name the index, it's sensible to name it after the field it is applied to, although
it can be practically anything
02:00 select the field names the index will be applied to
02:13 set the index property unique to true
02:50 open the table again and and add a duplicate record, and see that this is disallowed and a message is presented to the
user
03:30 now open the form and add a duplicate record note that you get the same error message as you did when you try to add a duplicate record in the table
04:00 note that this unique index also prevents the user entering more entries than appear in the combo-box. In other words there are seven selections available in the combo box, and only a maximum of 7 entries can ever be entered into the sub-form
Useful Links
… …
