How do I validate a field in Access to DEMAND unique values?

January 11, 2010 11:20 PM

MS Access Filter: How do I set a validation rule for a field in a form or table so that Access will immediately recognize that the same value has been used before and tell the user to generate a unique value?

I'm using MS Access 2003

I want to have a field (Quote_Number) which serves as the primary key for the QuoteTable, but I want Access to tell me as soon as the focus tries to leave the field if the value is not unique.

As it is Indexed and does not allow duplicates in the table (or form version) Access tells me that it can't allow a duplicate value as the record is completed, but obviously I'd like to be informed of that before the whole Record has been filled in.

I don't want to simply use an Autonumber value, as I want to be able to customise the quote number's for various purposes, and have used an Input Mask to ensure it is a letter followed by a string of three numbers (L999).

This is a noob question, and will either be slap in the face easy to rectify or Google (if I knew what to search for), or horribly complex and it is only my ignorance that leads me to believe this should be simple...either way, I appreciate any help...

posted by man down under to computers & internet (4 answers total)

Maybe an overly simple solution... but can you just make Quote_Number the first field of the record? Then if a duplicate is entered, you'd know right away before filling in the rest of the fields.
posted by LolaGeek at 5:43 AM on January 12, 2010

I don't want to simply use an Autonumber value, as I want to be able to customise the quote number's for various purposes, and have used an Input Mask to ensure it is a letter followed by a string of three numbers (L999).

Big mistake. A data value should mean one thing only. You're using the number both as a unique primary key, and "for various purposes". This is already biting you in the ass (by requiring you to validate its uniqueness), and will cause you more trouble in the future.

Use one hidden column for you synthetic key, and one or more additional columns for your "various purposes".
posted by orthogonality at 6:31 AM on January 12, 2010 [3 favorites]

I agree you should first add a real autonumber column as orthogonality suggests, but you will still need to validate this column.

Add an unbound textbox to the form with a query data source something like:
Select count(autonumkey) from foo where autonumkey <> Me!Autonum and QuoteCol=Me!Quote

Set the visible property of this textbox to false.

Now add an after update event to the Quote text box

Me!textbox.requery

If me!textbox >0 Then -- raise error

You would get slightly better performance writing a 'recordset query' as code in the afterupdate event, but the method above is quick and easy.
posted by Lanark at 12:08 PM on January 12, 2010

In case you still haven't found an answer, I think you can use the DLookup function in the Validation Rule for the field. In Help, look up the ValidationRule property; in there, they use an example that is exactly what you're trying to do.
posted by Simon Barclay at 6:09 PM on April 22, 2010