Create Check Duplicate Macro

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

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

Reply | 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

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

Reply | Quote

For Cancel Event, does it also cancel (abort) the record update?

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.

Reply | 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.

Thank you.
Armstrong

Reply | Quote