duplicate number when writing to table from form VBA

[nabiki]

Hi I have a form that writes to a table using the code below, the cardnumber field is "indexed no duplicates" but as the users will be completely non-savvy i would like to override the standard error and give them an error msg that says "This number already in database please try again" with an OK button that resets the field with a NULL value ready for new input...

Private Sub cardnumbernew_AfterUpdate()
Dim rst As New ADODB.Recordset
rst.Open "Giftcards", CurrentProject.Connection, adOpenKeyset, adLockPessimistic
 With rst
        .AddNew
        !Code = Me.codecard
        !cardnumber = Me.cardnumbernew
        !Date = Me.Date2
        .Update
        .Close
       
     
   
    End With
Set rst = Nothing
DoCmd.RunMacro "submitnewcard"
Me!cardnumbernew.SetFocus
End Sub

The "submitnewcard" macro just requerys the table as it displays below the input and resets all the fierlds as null.

 

[John Big Booty]

It would be more usual to validate user input in the Before Update event rather that trying to do it in the After Update event and letting the system do it for you as you will have far more control over the process.

 

[Pat Hartman]

You would trap the "duplicate" error in the form's on Error event.