The below code is an example of checking to see if last name exists in a table. This is taken from Access 2007 Inside Out page 1073.
You should buy the reference book - very helpful!
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rst as DAO.Recordset, strNames As String
'if on a new row
If (Me.NewRecord = True) Then
'check for similar names
If Not IsNothing(Me.LastName) Then
'open a recordset to look for similar names
Set rst = CurrentDb.OpenRecordset("SELECT LastName, FirstName FROM " & _
"tblContacts WHERE Soundex([LastName]) = '" & _
Soundex(Me.LastName) & "'")
'If got some similar names, collect them for the message
Do Until rst.EOF
strNames = StrNames & rst!LAstName & ", " & rst!FirstName & vbCrLf
rst.MoveNext
Loop
'done with the recordset
rst.Close
Set rst = Nothing
'see if we got some similar names
If len(strNames)> 0 Then
'Issue warning
If vbNo = MessageBox("contacts found with similar last names already saved in the database: " & vbCrLf & vbCrLf & _
strNames & vbCrLf & _
"Are you sure this contact is not a duplicate?", _
vbQuestion + vbYesNo + vbDefaultButton2, gstrAppTitle) Then
'cancel the save
Cancel = True
End If
End If
End If
End If
End Sub