Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How to create a Check Duplicate Macro?

    tblProperty contains the following fields
    PropertyID (autonum)
    BldgID
    Block
    Floor
    Flat

    I would like to make sure the combination of BldgID, Block, Floor, Flat won't be duplicated.
    In the Property Sheet of formProperty, I attach ChkDuplicate macro to the Before Update event.
    How to write such macro?
    If an existing record is found identical to the newly entered textboxes (controls)--BldgID, Block, Floor, and Flat, how can I stop adding the new record into the table? The entire Action Macros list don't the command to abort the record update.

    Armstrong

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd create an index in tblProperty on the combination of those fields, and set the Unique property of the index to Yes. That way, Access will not allow the user to create a duplicate combination in the table itself or in any query or form based on the table, without needing a macro or VBA code.

    BTW, the macro action to cancel an event is, surprisingly, CancelEvent.

  3. #3
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779773' date='14-Jun-2009 19:37']I'd create an index in tblProperty on the combination of those fields, and set the Unique property of the index to Yes. That way, Access will not allow the user to create a duplicate combination in the table itself or in any query or form based on the table, without needing a macro or VBA code.

    BTW, the macro action to cancel an event is, surprisingly, CancelEvent.[/quote]

    Thanks for your help. How to create an index of combined fields? Is it possible in Access?
    For Cancel Event, does it also cancel (abort) the record update?
    Armstrong

  4. #4
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='armsys' post='779832' date='15-Jun-2009 10:08']Thanks for your help. How to create an index of combined fields? Is it possible in Access?
    For Cancel Event, does it also cancel (abort) the record update?
    Armstrong[/quote]
    I found the solution for creating multiple-field index. In the Design View, Table Tools > Design > Indexes.
    At least it helps eliminate huge efforts in macro coding.
    HansV, thanks for pointing out the possibility.

    Armstrong

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='armsys' post='779832' date='15-Jun-2009 04:08']For Cancel Event, does it also cancel (abort) the record update?[/quote]
    If you use the Cancel Event action in a macro that is called in the Before Update event of a form, the record will not be updated.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2009
    Location
    Hong Kong
    Posts
    359
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='779841' date='15-Jun-2009 13:50']If you use the Cancel Event action in a macro that is called in the Before Update event of a form, the record will not be updated.[/quote]
    Thank you.
    Armstrong

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •