How do you warn the user if a record exists?

[martinr]

What is the best way to warn a user if a record already exists?
I've looked at some examples that use an SQL SELECT statement with a recordset
to compare existing records against the one the user is entering but i'm not sure which event this should be triggered by - eg; would it be the control's 'After Update' ,on 'Dirty' or 'Change' event?
Would the record have been saved when the 'After Update' is fired- if not then it would not be found in the recordset and the SQL would not work....

Does anyone know the how to create a textbox function that automatically searches and completes the textboxt based on exisitng records (like the cell autocomplete feature in Excel)? This could then populate the form with the existing record fields (if found ) and the user could update the record if necessary?
This is slightly different to my first question but could stop duplicate records being enetered.

 

[Mihail]

There are 2 questions in your post.
1) If you only wish to avoid duplicates then, at table level, create an Index (using the Indexes window) with the property Unique. This way Access will handle the things for you. Any time when the user will try to save a duplicate record, an warning message (default for Access) will appear and the record will not be save.
2) If you wish to have a custom message usually is used the DCount function in the Before Update event.
Cancel = (DCount(...) > 0)