locked
Best Method RRS feed

  • Question

  • I have a table with 5 fields that I am concerned with. 

    I was going to concatenate the data in those five fields to create a new field.  I was going to make this new field the primary key, as that concatenation will create a unique value that will never be duplicated.  However, in a separate post, it was strongly suggested by many, that this was not a good Idea.

    What I need to do now, is assure that when a user creates a new record, s/he does not cause a duplicate record to be created. 

    I was going use the after update event to create my concatenated new primary key and see if that key already exists.  If it did, I would not allow the entry.

    Since everyone suggested that my primary key was not a good idea, I don't have any idea how to do this check.  Any suggestions?

    Wednesday, December 2, 2020 8:25 PM

Answers

  • As others have suggested, use an AutoNumber field as primary key.

    Then create a composite index on the 5 fields, and set the Unique property of this index to Yes. Users won't be able to enter duplicate records on the combination of those fields.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Wednesday, December 2, 2020 9:03 PM
    • Marked as answer by tkosel Thursday, December 3, 2020 11:28 PM
    Wednesday, December 2, 2020 9:02 PM

All replies

  • As others have suggested, use an AutoNumber field as primary key.

    Then create a composite index on the 5 fields, and set the Unique property of this index to Yes. Users won't be able to enter duplicate records on the combination of those fields.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    • Edited by Hans Vogelaar MVPMVP Wednesday, December 2, 2020 9:03 PM
    • Marked as answer by tkosel Thursday, December 3, 2020 11:28 PM
    Wednesday, December 2, 2020 9:02 PM
  • Since everyone suggested that my primary key was not a good idea, I don't have any idea how to do this check.  Any suggestions?

    Hi tkosel,

    For creating new records, I use an unbound New-form for entering the values.

    When the Save-button is pressed, a number of checks are done, among others whether values are obligatory, fall in certain ranges, but also if some combinations of values may already exist in the table.

    If all checks are passed, a new record is created with an autonumber as PrimaryKey, and the corresponding fields are filled. With no-pass there is no new record created, a message is generated, and the control with the problem-field gets the focus. This way of working ensures that only "correct' data is entered, while at the same time the minimal locking of recordblocks occurs.

    In this case it is also not necessary to have an extra "redundant" field with an unique key.

    I said when pressing the Save-button, in fact these checks are done in the BeforeUpdate event of each control on the New-form. The user gets a signal in a very early stage of the inut process.

    In case of data-input through code (no user activity) there is no BeforeUpdate event. In that case the same checks are done, including if some combinations of fields already exists. If this exists, the corresponding Id (Autonumber) is returned; if it not exists, a new record is created, and the new Id is returned.

    This is my best method. A further detail is that my New-form is a generalized form, so it works for any table in any of my applications. But it can also work with a dedicated New-form per table.

    Imb.

    Wednesday, December 2, 2020 10:54 PM
  • Hans,

    Thanks for your information.  If I don't like the access generated message as a result of not being unique, can I use a on error event to replace it?  So where would I use that event?

    Thursday, December 3, 2020 11:31 PM
  • You could use the Before Update event of the form to check whether the combination of the 5 fields occurs in the table for values of the autonumber field <> the value of the autonumber field in the current record.

    If so, display your own message and set Cancel to True.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Thursday, December 3, 2020 11:34 PM