Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 23rd, 2004, 05:25 PM
Registered User
 
Join Date: Jan 2004
Location: Jacksonville, FL, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to roznix
Default Check for Duplicate Records in Data Entry Form

I need help with a data entry form in Access 2000. I have a database to track paper documents. Upon adding a new record, and before saving it, I want to warn the user that a potential duplicate may exist based on the following logic:

If the SponID, PatID, and DocTypCd in the current form equal the information in an existing record where the DCN's are different, display a warning message for the user to check the records for possible duplicates. The SponID, PatID, and DocTypCd are all text fields; the DCN is an autonumber field as well as primary key for the table "tblDocTracMain".

Below is the code I've tried to use, but it is not working for me. I get the run-time error 2471, indicating that the object doesn't contain automation object "DocTypCd" (value in this field). The DocTypCd field is also a combo box with a separate table as the row source, however, the control source is in "tblDocTracMain".

Private Sub PatID_AfterUpdate()
Dim strWhere As String
Dim varDCN As Variant
    strWhere = "[tblDocTracMain].[DCN] <> " & Me!DCN
    strWhere = strWhere & " AND [tblDocTracMain].[SponID] = " & Me!SponID
    strWhere = strWhere & " AND [tblDocTracMain].[PatID] = " & Me!PatID
    strWhere = strWhere & " AND [tblDocTracMain].[DocTypCd] = " & Me.DocTypCd & ""
    varDCN = DLookup("DCN", "tblDocTracMain", strWhere)
If Not IsNothing(varDCN) Then
        MsgBox "Warning: One or more records exist for this beneficiary and document type, please ensure this is not a duplicate!"
End If
End Sub

Any help is greatly appreciated. Thanks.

 
Old January 28th, 2004, 11:09 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Just create an unique constraint on that field on the table. Try not to use an autonumber for a primary key. You may look into creating a primary key compossed of all of your unique values.



Sal
 
Old April 28th, 2011, 08:24 AM
Registered User
 
Join Date: Apr 2011
Location: USA
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Check for Duplicate Records in Data Entry Form

Data Entry must contain unique data so when we search then find easily.Primary key use in data is unique.By using this we search quickly.
 
Old May 2nd, 2011, 04:20 AM
Registered User
 
Join Date: May 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Re:Check for Duplicate Records in Data Entry Form

Dear roznix

just change the code


Private Sub PatID_AfterUpdate()

if Dcount("[Ojectname in table]","table name","[obeject name in table]=[object name in form]")>0 then
msgbox = (Print Message)

End if
 
Old May 9th, 2011, 02:16 AM
Registered User
 
Join Date: Apr 2011
Location: USA
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Check for Duplicate Records in Data Entry Form

The data entry form contains several tools along the right hand side that makes it easy to find and correct or delete records from the database.
 
Old June 7th, 2012, 08:53 AM
Registered User
Points: 3, Level: 1
Points: 3, Level: 1 Points: 3, Level: 1 Points: 3, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Go through this link will help to check out the duplicate records in data entry forms. We all know that accuracy is very important in data entry.




Similar Threads
Thread Thread Starter Forum Replies Last Post
The best way to design a data entry web form cesemj ASP.NET 2.0 Basics 2 November 5th, 2007 02:44 PM
Duplicate records while inserting the data ebindia0041 .NET Framework 1.x 0 May 23rd, 2007 07:15 PM
avoid duplicate entry of records mikersantiago Classic ASP Basics 4 February 10th, 2005 09:14 PM
New Record Data entry one form lgpatterson Classic ASP Basics 1 February 8th, 2005 12:58 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.