When using indexes to prevent a repeat a complex error message
appears. I would like to change this for one of my own. Can it be
done?
You are quite right to want to prevent the user seeing database engine
error messages. DB error messages are to protect the data; your
application is there to protect the user!
You will need to anticipate any occasions that might cause a db engine
error. In the example you mention, this is usually at record INSERT time.
As long as the users are using a form, then you can intercept a suitable
event (Form_BeforeUpdate, Form_RecordInsert, etc) and test the database
first. If you allow users to change the value in a key field, then you
need to intercept that as well (txtUserID_BeforeUpdate and so on).
The fastest method to see if a key value already exists is probably the
DCount() vis:
If 0<DCount("*", "MyTable", "KeyField=" & txtMyKeyField.Value) Then
' it's there, so warn the user and refuse the Update etc
Hope that helps
Tim F
