Issues with BeforeUpdate on a form

[cmf2112]

Hello all. I generally am able to find what I need on here without asking for additional help, but this one has me a bit stumped. I am trying to prevent users from putting in a duplicate email address on a form—if they input a duplicate, they should get a message telling them so. I was able to put together the following code:

Private Sub Rejected_Email_Address_BeforeUpdate(Cancel As Integer)

Dim Answer As Variant
Answer = DLookup("[Rejected_Email_Address]", "tblMainTable", "[Rejected_Email_Address] = '" & Me.Rejected_Email_Address & "'")
If Not IsNull(Answer) Then
MsgBox "Email address already logged", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

Cancel = True
Me.Rejected_Email_Address.Undo

Else:
End If
End Sub

The problem is, when I add an email address that I know is not currently stored in the table, I still get the message box and the table is not updated. I’ve been staring at this for some time and I’m guessing I’m just missing something fairly obvious—this is one of my first attempts at VB code. Any suggestions?

TIA!

 

[boblarson]

By the way you don't need the ELSE for an IF if there isn't one. Also, you don't use the colon after it either.

Try this:

Private Sub Rejected_Email_Address_BeforeUpdate(Cancel As Integer)

If DCount("[Rejected_Email_Address]", "tblMainTable", "[Rejected_Email_Address] = " & Chr(34) & Me.Rejected_Email_Address & Chr(34)) > 0 Then
   MsgBox "Email address already logged", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"

   Cancel = True
   Me.Rejected_Email_Address.Undo

End If
End Sub

 

[cmf2112]

Thanks Bob. Unfortunately I'm still having the same issue. I really appreciate your help, I'll keep working on it.

 

[boblarson]

Are you sure that this part:

"[Rejected_Email_Address] = " & Chr(34) & Me.Rejected_Email_Address & Chr(34)) > 0

is the name of the control on your form?