Solved

Trap Duplicate Entries On First Field

Posted on 2008-10-01
7
423 Views
Last Modified: 2013-11-28
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!
0
Comment
Question by:Gazaway
  • 3
  • 3
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22618825
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
 
LVL 2

Expert Comment

by:rene119
ID: 22619042
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
 

Author Comment

by:Gazaway
ID: 22619286
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Gazaway
ID: 22619391
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22619402
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 22619423
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
 

Author Comment

by:Gazaway
ID: 22619476
Perfect! I used the Exit event and your code and it works great! Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now