check for duplicates

on a continuous form how do I check for duplicate values in a particular control before adding a new record?
eliwilAsked:
Who is Participating?
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
" was missing...

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

Mike
0
 
rockiroadsCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
eliwilAuthor Commented:
can I check two fields, e.g. lngCntctID and lngGrpSubID? in tblCntctSubGrp ?
0
 
rockiroadsCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
rockiroadsCommented:
Mike, no comma required in the filter


0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Thanks  rockiroads,

Dcount("*","tblCntctSubGrp ,"[lngCntctID]=" & txtlngCntctID & " And lngGrpSubID=" & txtlngGrpSubID)
0
 
eliwilAuthor Commented:
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
 
rockiroadsCommented:
EliWil

get rid of the comma
0
 
eliwilAuthor Commented:
Removed comma. Now getting Expected: list separator or ) and the text hilited is lngCntctID
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.