Trap Duplicate Entries On First Field

There is a table in my database that contains information on clients (tblClients). Two fields in the table - ClientName and ClientID - are indexed, no dups allowed. The client name is the first field in the form and I would like to trap it if it is a duplicate of an exisiting name, before any more fields are entered. I've tried a macro for On Lost Focus, On Dirty, etc., but nothing has worked. Please advise!
GazawayAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
private sub clientname_beforeupdate(cancel as integer)

if dcount("ClientName","tblClients","[clientName]='" & me.clientName &"'")>0 then
msgbox "Duplicate client name found"
cancel=true
me.clientname.set focus
end if

end sub


if you want to use the exit event

private sub clientname_exit(cancel as integer)

if dcount("ClientName","tblClients","[clientName]='" & me.clientName &"'")>0 then
msgbox "Duplicate client name found"
cancel=true

end if

end sub
0
 
Rey Obrero (Capricorn1)Commented:
what code are you using in those events you mentioned above?

have you tried using dcount or dlookup function

if dcount("ClientName","tblClients","[clientName]='" & me.clientName &"'")>0 then
msgbox "Duplicate client name found"
end if
0
 
rene119Commented:
Hello Gazaway,

Here is one way to do it.
On your form in the on exit event for the Client Name field add the code below.
The only thing you will have to check is that you have Microsoft DAO  enabled in your references.
To check go to modules and select new then click on Tools at the top of the screen and select references look in the list and make sure there is a check mark in front of the Microsoft DAO. If you have multiples select the hightst version I use 3.6. Then exit the module.
Let me know if you have any questions.

Rene
Private Sub ClientName_Exit(Cancel As Integer)
    'variables
    Dim db As Database
    Dim rec As DAO.Recordset
    
    'run the procedure only if you are on a new record
    If Me.NewRecord = True Then
    
    'set our objects
    Set db = CurrentDb()
    Set rec = db.OpenRecordset("tblClients")
    
    'move to the first record in the table
    rec.MoveFirst
    
    'go through each record in the table
    While Not rec.EOF
        'If the client name in the table matches the client name in the form field
        'display a message, move the focus back to the client name field and exit this code
        If rec![ClientName] = Me.ClientName Then
            MsgBox Me.ClientName & " is a duplicate name!"
            Cancel = 1
            'Me.ClientName.SetFocus
            rec.Close
            Set db = Nothing
            Exit Sub
        End If
       
       'move to the next record in the table
        rec.MoveNext
    Wend
    
    'clean up
    rec.Close
    Set db = Nothing
    
    End If
 
End Sub

Open in new window

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
GazawayAuthor Commented:
capricorn1,

I had read in another question that "requery" of the field in question would work. So my macro is a simple requery with msgbox on error.

Where would I put the code you suggest?
0
 
GazawayAuthor Commented:
I put capricorn1's suggested code in the "On Exit" event and it works! However, I'd like the focus to return to the client name field so it can be corrected, instead of moving to the next field. I tried adding this command: DoCmd.GoToControl "txtClientName", but it still goes to the next field in the form instead of back to txtClientName.

rene119, I'm going to stick with capricorn1's method for now, since it works and is less complex.
0
 
Rey Obrero (Capricorn1)Commented:
place it in the before update event of the control clientname


private sub clientname_beforeupdate(cancel as integer)

if dcount("ClientName","tblClients","[clientName]='" & me.clientName &"'")>0 then
msgbox "Duplicate client name found"
cancel=true
end if

end sub
0
 
GazawayAuthor Commented:
Perfect! I used the Exit event and your code and it works great! Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.