You are here:

Using MS Access/Access - use of if command in code builder


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.

Hi Pankaj,

Many thanks for the question.

See if this works for you.

Private Sub DiaryNo_AfterUpdate()
On Error GoTo ErrorHandler

Dim strCriteria As String

strCriteria = "[DiaryNo] = " & Me!DiaryNo

Me.RecordsetClone.FindFirst (strCriteria)
  If Me.RecordsetClone.NoMatch Then
   MsgBox "No entry found"
   MsgBox "Already exists"
End If

Exit Sub
MsgBox "Error Number: " & Err.Number & " " & Err.Description, , "Error Message"
End Sub

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Julie Misson


My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.


I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the website.

Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 All rights reserved.