Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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.

Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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.
Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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.
Reply With Quote
  #6 (permalink)  
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 07:20 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.