CODEWITHSUNDEEP
vbams-access-2007
Hold The Door
Hold The Door

Reputation: 39

Access 2007, VBA: Checking for duplicates, have null field

I want to check if a field has duplicates from my EMP table. Everything works except I need to create an exception when there's nothing in the field aka null exception.

Private Sub cmdDuplicates2_Click() 
Dim Name As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("EMP", dbOpenDynaset)
Name = Me.Personnel
rst.FindFirst "[Personnel] = '" & Name & "'"
If rst.NoMatch Then
        MsgBox "No duplicates found"
  Else
        MsgBox "Name is already in Database"
End If

End Sub

EDIT: Doing this now. If it's null then it says "Please enter a name." But it also says "No duplicates found." I want it to just say "Please enter a name." if the field is blank.

Private Sub cmdDuplicates2_Click()
Dim Name As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("EMP", dbOpenDynaset)

If IsNull(Me.Personnel) Then MsgBox "Please enter a name." Else: Name = Me.Personnel

rst.FindFirst "[Personnel] = '" & Name & "'"
If rst.NoMatch Then
        MsgBox "No duplicates found"
  Else
        MsgBox "Name is already in Database"
End If

End Sub

Upvotes: 0

Views: 1984

Answers (2)

Mike Lowery
Mike Lowery

Reputation: 2868

Do something like this:

If IsNull(Me.Personnel) Then Name = "Empty" Else Name = Me.Personnel

Upvotes: 1

PowerUser
PowerUser

Reputation: 11791

Try this. In this version, I'm actually counting the number of records for each Personnel value:

Private Sub cmdDuplicates2_Click() 
Dim rst As DAO.Recordset
Dim Qry as string
Qry="Select count(*) from EMP where [Personnel]='" & Me.Personnel & "'"

Set rst = Currentdb.OpenRecordset(Qry, dbOpenDynaset)

If rst.fields(0)>1 then
        MsgBox "Name is already in Database"
  Else
        MsgBox "No duplicates found"
End If

End Sub

Edit

Sorry, my first instinct was to just refactor your code the way I would do it. Try adding this to the beginning of your procedure:

If Me.Personnel="" then 
    msgbox "Please enter a name."
    exit sub
end if

Upvotes: 0

Related Questions