Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Location
    India
    Posts
    42

    Question Unanswered: Duplicate Value error

    Hi,

    I am facing a problem while preventing duplicate value in field "CID". As per below code, it response good when any duplicate value enters in CID field, but it also responses the same msg while entering new CID value.

    Please help me to find the problem. thanks in advance.
    I'm using Access2003.
    ================================================== ========
    Private Sub CID_BeforeUpdate(Cancel As Integer)

    Dim SID As String
    Dim stLinkCriteria As String
    SID = Nz(Me.Cid.Value, "")

    stLinkCriteria = "Cid = " & "'" & SID & "'"

    If DCount("Cid", "tblCustomerSale", stLinkCriteria) > 0 Then
    MsgBox "Duplicate Customer ID.", , "Error"
    Cancel = True
    End If
    End Sub
    ================================================== =======

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Are you absolutely sure that you're entering a new CID number? Have you checked the Table to varify that the CID you're going to enter is, in fact, new?

    I ask because I created a db with your Field Name, copied and pasted your code in the Code Module of the Form, and, for me, also using 2003, it works as it should, only popping the Messagebox when I attempt to enter an existing CID.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jan 2012
    Location
    India
    Posts
    42

    Exclamation Same Error

    Thanks for fast reply dear, but I' facing the same problem. I'm getting msgbox in both conditions, whether I enter new CID value or existing Value.

    Many Thanks,
    Anuj

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Ditto. I tried too and, as expected, I got the same result as Missinglinq.
    Have a nice day!

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What about changing the dcount >1

    As you are checking if more than one
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Jan 2012
    Location
    India
    Posts
    42

    Thumbs up Duplicate Error problem solved..

    Finally, I got the solution. It is working in both ways. It is a below:
    =================
    Private Sub CID_BeforeUpdate(Cancel As Integer)
    Dim stLinkCriteria As String

    If Me.NewRecord Then
    stLinkCriteria = "CID = """ & Me.CID & """"
    Else
    stLinkCriteria = "CID = """ & Me.CID & """"
    End If

    If DCount("*", "tblCustomerSale", stLinkCriteria) > 0 Then
    MsgBox "Duplicate Customer ID.", vbExclamation, "Error"
    Cancel = True
    End If
    End Sub
    ===================

    Thanks a lot for your fast replies.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This makes little sense. The way the criteria is assembled is the same whether Me.NewRecord is True or False:
    Code:
    If Me.NewRecord Then
        stLinkCriteria = "CID = """ & Me.CID & """"
    Else
        stLinkCriteria = "CID = """ & Me.CID & """"
    End If
    is the same as:
    Code:
    stLinkCriteria = "CID = """ & Me.CID & """"
    and the test is useless.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •