Using MS Access/Access Query


I am having a table named dup_deg and having a field diaryno whose datatype is 'number' which is indexed as 'yes (no duplicates). Now for filling the data, I have provided a form based on table dup_deg for the user. Diaryno is the first field of the table as well as of the form. I want that when the user fills the diaryno in the form, than it should check immediately that whether the same is available or not. Since it is indexed, it will not take duplicate value but the form is informing the user only at the end of the form. I want that once the user fills the value than 'after update' system should check and inform the user there itself that 'this number already exists'.

I tried this code after update(). If [DNO] = [DIARYNO] then
Msgbox (Record already exists)
[DNO] = “”
BUT this code is right upto the message and after I click OK than (1) it does not clears the data in DNO field and (2) it goes to tab 2 i.e. to the next field.

I am not much expert in writing script with code builder, can you tell me any easy solution for this.

You could write code that saves the record at that point which would initiate the record rejection but this would only work if there were no other fields that where required fields in the table. A more full proof approach would be to open a record set based on finding the previous record with that number and if the records that showed a record in it you could pop up a message box saying that that number was already used. If you need help in records set code my contact information is on my site. Sorry I was on vacation and couldn't get to this question sooner. Your friend Bob Heifler of  

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Bob Heifler


Free Microsoft Access Tutorial Videos are available to you at my web site. Fifteen years of Microsoft Access experience allows me to answer most of your questions. Common question example files available here.


Microsoft Access programming since 1994. Support for all versions.

My Microsoft Access articles are published

UCLA GRAD, Member of Association of Database Developers

Awards and Honors

Past/Present Clients
See some of my past clients here.

©2017 All rights reserved.