The typical way one handles this situation is to use the form's BeforeUpdate
event to test if a duplicate record would be created if the form saves the
data. You note that you're entering a [Member #] value. The code in the
BeforeUpdate event would look something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("Member #", "NameOfYourTable") > 0 Then
   MsgBox "Member # " & Me.NameOfControlHoldingMemberNumber.Value _
       & " already exists in the database!", vbExclamation, "Duplicate
Member #"
   Cancel = True
End If
End Sub

The above code will test to see if the member number already exists in the
table; if yes, it cancels the save of the data and tells the user. If it
doesn't, the form saves the data with no message.

Also, I note that you're using the # character in the name of a field in a
table. It and many other characters should not be used because the they have
special meanings for ACCESS and Jet. See this Knowledge Base article for
more information about characters that should not be used:

    Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763