In access 2003 when using the input form in our customer database we would
like to gat a warning if we enter a duplicate address line.
You can set a Unique Index on the form's underlying table. This would cause Access to alert you when a user tries to
enter an exact duplicate, and will disallow the entry. To set a unique index, open the table in design view, click View
- Indexes, then add a new index named something like UniqueAddress and set the Unique property to Yes. Select the
appropriate column in the Field Name column. To add additional columns to this index, select additional column in the
second column but do NOT add a new Index name, so your index dialog would look like this:
Index Name Field Name Sort Order
-------------------------------------------------------
UniqueAddress sStree Ascending
sCity Ascending
sZip Ascending
AnotherIndex lCustomer Ascending
In the crude depiction above, the UniqueAddress index would take 3 fields (sStreet, sCity and sZip) and combine them to
form a new Index. With a setting of Unique = Yes, then you would not be able to add a record if the exact values being
input are identical to any other record.
Be careful with this, due to data entry anomalies. A record containing 123 Main Street, Anytown, 99909 would NOT be the
same as a record containing 123 Main ST, Anytown, 99909 (even though we humans realize it's the same thing).
Also: the default error thrown by Access is pretty clear, but isn't what you want endusers to see. If you use error
handling, you can intercept the default error messae and include your own. I can't recall the exact error number, but if
you build your unique index and attempt to insert a duplicate address Access will provide it for you.
Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com