none
Preventing duplicate records RRS feed

  • Question

  • I am trying to create a database for a school. I have a form, frmSchoolYears and a subform, frmYearClassesSubform for the purpose of entering data to create new classes for a school year. The data is to be entered in a table called tblYearClasses. In the subform the data is to be entered with three combo boxes: cboSchoolYearID, cboClassCode, cboStaffID

    The corresponding fields in the table are: SchoolYearID, ClassCode, StaffID. The data types are Long Integer, Short Text and Long Integer respectively. The intention is to have only unique combinations of the three fields in the table. I am informed that this could be done using code in the form's BeforeUpdate event. I will like someone to give me the code that will do this. If the combination already exist I will like the code to give a message to the user and prevent the update. Thank you in advance for any help given.

    Gordon

    Thursday, July 13, 2017 10:53 PM

Answers

All replies

  • Hi Gordon Swan,

    You could create an unique constraint on the three fields, so combinations of the three fields will be unique and when you try to insert/update a duplicate records, you will get an error warning. We could use Form Error event to catch the error and customize it.

    Here is the example for setting unique constraint and code in Error Event.


    Private Sub Form_Error(DataErr As Integer, Response As Integer)

    If DataErr = 3022 Then

    Response = acDataErrContinue

    MsgBox "Current Data Combination exists!"

    End If

    End Sub

    You could refer to below links for more information.

    https://stackoverflow.com/questions/5635811/unique-constraint-on-multiple-fields-in-access-2003

    https://stackoverflow.com/questions/13258494/how-can-i-have-a-custom-message-for-a-duplicate-key-entered-in-a-form

    Best Regards,

    Terry

    • Marked as answer by Gordon Swan Friday, July 21, 2017 3:32 PM
    Friday, July 14, 2017 3:11 AM
  • You should do this by defining the three columns as a candidate key by including them in a unique index as Terry describes.  Alternatively you can dispense with the surrogate autonumber key and make the three columns the composite primary key.  A surrogate key serves no real purpose in a table which models a ternary relationship type like this.

    However, you almost certainly will need three further tables, one to model the relationship type between school years and classes, with a primary key of SchoolYearID and ClassCode, another to model the relationship type between school years and staff, with a primary key of SchoolYearID and StaffID, and another to model the relationship type between classes and staff, with a primary key of ClassCode and StaffID.  Each possible combination of two columns in the three-column primary key of  tblYearClasses is then a foreign key referencing the two-column primary key of one of the three additional tables.  By creating enforced relationships between tblYearClasses  and each of the three tables on the two-column keys the validity of rows entered into tblYearClasses is then ensured.

    My StudentLog demo, whose model is illustrated below includes a number of similar relationship types.



    You'll find the demo as StudentLog.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    If you study the forms in the demo carefully you'll also find that the insertion of invalid data is also prevented in many cases by means of combo boxes whose lists are restricted on the basis of other selections in the forms/subforms.


    Ken Sheridan, Stafford, England

    Friday, July 14, 2017 11:27 AM