?
Solved

check for duplicates

Posted on 2006-06-05
11
Medium Priority
?
261 Views
Last Modified: 2008-03-03
on a continuous form how do I check for duplicate values in a particular control before adding a new record?
0
Comment
Question by:eliwil
  • 4
  • 4
  • 3
11 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 16835932
one of two ways

either use a DCOUNT using the field in question plus any filters u may have on the form
e.g.

DCOUNT("fld","table","filter")

or in code

0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16835968
Lets say you want add a company name called ABC Inc.  The procedure below would check it for duplicates before adding.  If there this company name alredy exist, it will not add it.  

The code goes to the subfrom's befire update event:

If Dcount("*","TableName,"[Company]=" & chr(34) & txtCompany & chr(34))>0 Then
    MsgBox txtCompany & " already exists."
  cancel=True
End if

Also, you can add index (no dupplicate at table level) for this field to prevent dupplicate enteries.

Mike
0
 

Author Comment

by:eliwil
ID: 16836479
can I check two fields, e.g. lngCntctID and lngGrpSubID? in tblCntctSubGrp ?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Expert Comment

by:rockiroads
ID: 16836612
yes, u just put it in the filter in DCOUNT

like I said, u need to use the same filter as per your current view of records to ensure u get the right count
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16838537
Dcount("*","tblCntctSubGrp ,"[lngCntctID]=" & txtlngCntctID & ", And lngGrpSubID=" & txtlngGrpSubID)

Where, txtlngCntctID and txtlngGrpSubID are text boxes on the subform containing values for lngCntctID and lngGrpSubID, respectively.

mike
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16839867
Mike, no comma required in the filter


0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 16839918
Thanks  rockiroads,

Dcount("*","tblCntctSubGrp ,"[lngCntctID]=" & txtlngCntctID & " And lngGrpSubID=" & txtlngGrpSubID)
0
 

Author Comment

by:eliwil
ID: 16840022
I'm getting a syntax error. Double checked field names and control names. Can you see what's wrong?

    If Dcount("*","tblCntctSubGrp,"[lngCntctID]=" & cboCntctID & ", And lngSubGrpID=" & cboSubGrpID) then
        MsgBox "This record already exists. You can use the form's abort command button to back out of this record."
    End If
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16840119
EliWil

get rid of the comma
0
 

Author Comment

by:eliwil
ID: 16845127
Removed comma. Now getting Expected: list separator or ) and the text hilited is lngCntctID
0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 2000 total points
ID: 16847513
" was missing...

Dcount("*","tblCntctSubGrp" ,"[lngCntctID]=" & txtlngCntctID & " And lngGrpSubID=" & txtlngGrpSubID)
                                       ^--- here
Sorry about that.

Mike
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

809 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