Dupicate checking works but when no duplicate record, Data Entry is added in table

[yaozaah04]

Hello, I am working on a data entry form using Microsoft Access 2010 and I am supposed to give the user a separate option to look for a duplicate record. However the duplicate record-checking works but when there is no duplicate record, the form automatically adds the record onto the table, but I have a separate cmdAdd_Click() for that. I think it has something to do with the "vbOKOnly" Button in my MsgBox but I need some insight as to what to do.

Background Info:
Table Name =MainDrawingRec ords
Field Name = Record Number
Object Name= txtRecordNumber
----------------------------------------------

Private Sub cmdDuplicate_Click()
'Check if there is a duplicate Record Number and if there is then form allows additions but does not add in table
Dim Ans As Variant
Dim Ans2 As Variant

    If DCount("[Record Number]", "MainDrawingRecords", "[Record Number]=[txtRecordNumber]") > 0 Then
       Ans = MsgBox("There is a duplicate Drawing Record Number found!", vbRetryCancel)
        'Lets the User retry to enter the data again
        If Ans = vbRetry Then
             Me.Undo
        'If the User cancels the data entry, it still lets the User view what he/she entered in the form
        Else
            Cancel = True
        End If
    'If there is no duplicate data entry then the form does nothing and lets the user conitnue and click cmdADD Button
    Else
       Ans2 = MsgBox("No duplicate Drawing Record Number found.", vbOKOnly)
        If Ans2 = OKOnly Then
            Cancel = True 'Me.CancelEvent does not work here
        End If
    End If
End Sub

[Rabbit]

If you're going to do duplicate checking, it needs to happen in the BeforeInsert event. By itself, a button click event has no control over whether or not a record is inserted.

As for line 18, that check makes no sense. You only allow them to click OK anyways, so why check for it at all.

[NeoPa]

Before Posting (VBA or SQL) Code helps you to get some of the basics sorted before even needing to come and ask questions on your code. Please find and fix the more obvious compile errors before trying to continue. The linked article will help you with that. Cancel & OKOnly have no meaning in the context you're trying to use them, but you'll find and fix these more easily by following the linked instructions.