Duplicate value

[brin]

How can i set a field so when data is entered it alerts the user that there is already the same data in the database. I know i can do this in the table settings but the prompt only pops up after i have entered all of the other fields on the form, i would like the prompt to appear as soon as you leave the field??

 

[wh00t]

If the field on the form is a combo box, you can simply set the limit to list property to yes. You could also customize the error message if the standard message is too 'jargon' for the users.

 

[David R]

http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=88626 should give you the help you need. I had the same issue in my database a month or so ago.

 

[wh00t]

Just for reference, brin mailed me, and I sent him the following script to add to the On Exit event for the ID field (which is the primary key). As the field is primary key, at automatically blocks duplicates, but doesn't warn untill all fields are complete, so part of the code is refresh form on exit which would validate the ID, if the ID was a duplicate a runtime error message would appear so the rest of the script is for error handling and to present a more 'user friendly' error message

open the properties on the form for the ID field and select code builder on the OnExit event
between the sub and end sub statements insert the following code

On Error GoTo Err_Form_KeyDown

Const conErrNotUpdatable = 3022

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Form_KeyDown:
Exit Sub

Err_Form_KeyDown:
If Err.Number = conErrNotUpdatable Then
MsgBox ("ID already exists, please enter new ID"), vbExclamation

Else
GoTo Exit_Form_KeyDown
End If
Resume Exit_Form_KeyDown