Using DCount for duplicate records

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Dkpitbull
    New Member
    • Mar 2014
    • 4

    Using DCount for duplicate records

    Hi guys!
    I'm new in the forum and new Access user as well. I have a form where users can insert new data to a table through it. I kow how how to use Dlookup but not familiar with DCount. I want my vba code to prevent the user of using a combination of values that have already been entered. More specific...

    My table with name "Company" has three columns that I need to check.
    1) column 1 - Company Name
    2) column 2 - Street
    3) column 3 - Post Code
    The user will be able to enter a new record with only one of these to be the same with a previous one. If a same combination of two of them already exist in the table a message of duplicated record will appear.

    code: "What I tried, but does not work"

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim Street As String
    Dim Company_Name As String
    Dim Town As String
    If DCount("[Street]", "Company", "[Company_Name]='" & Me!Company_Name & "' AND [Town]='" & Me!Town & "'") > 0 Then
        msg = "You already have that OrderID and Customer_ID combination" & vbNewLine
        msg = msg & "The record will now be undone"
        MsgBox msg, vbExclamation, "System Duplication Message"
        Me.Undo
        Cancel = True
    End If
    End Sub
    Help please!!!!
    Last edited by Rabbit; Mar 12 '14, 06:04 PM. Reason: Please use [code] and [/code] tags when posting code or formatted data.
  • Seth Schrock
    Recognized Expert Specialist
    • Dec 2010
    • 2965

    #2
    What do you mean "doesn't work"? Do you get an error message, does it not work as expected, etc.

    Comment

    • Dkpitbull
      New Member
      • Mar 2014
      • 4

      #3
      Hi Seth!
      Thanks for your reply!
      What I get is: 'Run-time error '2465' Microsoft Access can't find the field 'Company_Name'

      In my table the field is 'Company Name'
      I think that I don't define the DCount correctly..

      Comment

      • Seth Schrock
        Recognized Expert Specialist
        • Dec 2010
        • 2965

        #4
        In your DCount() function, you have an underscore between the words 'Company' and 'name'. You need to have it the same as what is in your table. Based on the error message, I would say that without the underscore is the correct version.

        The DCount() and DLookup() functions have the exact same syntax. The difference is what it returns.

        Comment

        • Dkpitbull
          New Member
          • Mar 2014
          • 4

          #5
          I tried it with space like "Company Name" and hits as an error in the compiler.When I use Dlookup I set it like this:


          Code:
          Private Sub Part_Ref_BeforeUpdate(Cancel As Integer)
          Dim NewCompanyName As String
          Dim stLinkCriteria As String
          NewCompanyName = Me.Company_Name.Value
          stLinkCriteria = "[Company_Name] = " & "'" & NewCompanyName & "'"
          If Me.Company_Name = DLookup("[Company_Name]", "Companyform", stLinkCriteria) Then
          MsgBox "This " & NewCompanyName & ", has already been entered in database." _
          & vbCr & vbCr & "Please check company info again.", vbInformation, "Duplicate Information"
          Me.Undo
          End If
          End Sub
          the above code works perfect but I need to check two values each time. I tried what you recomended without space and it doesn't work again.
          Let me make it clear:
          My form's name is 'Companyform'
          My table is called 'Company'
          And the fields of the table that I don't want to be the same are: 'Company Name' , 'Street', 'Town'

          Thanks for your reply again Seth!!
          Last edited by Rabbit; Mar 12 '14, 06:03 PM. Reason: Please use [code] and [/code] tags when posting code or formatted data.

          Comment

          • Seth Schrock
            Recognized Expert Specialist
            • Dec 2010
            • 2965

            #6
            Something isn't making sense as your code is using the form name in the Domain parameter which isn't valid per Microsoft's website. What is the exact error you get when you use 'Company Name' with just a space instead of using the underscore?

            Comment

            • Dkpitbull
              New Member
              • Mar 2014
              • 4

              #7
              Access doesn'it allow it. the code in the compiler turns red..
              Do you think I can use the Dlookup code that I sent you with replacing Dlookup with DCount?

              Comment

              • Seth Schrock
                Recognized Expert Specialist
                • Dec 2010
                • 2965

                #8
                No, I don't think that would work. Please post the code that turns red. The compiler doesn't know if the field names are correct, so there must be a different problem with that. When you post your code, please use the [CODE/] button at the top of the text editor and place your code between the code tags

                Comment

                • GKJR
                  New Member
                  • Jan 2014
                  • 108

                  #9
                  Have you tried Debug.Print on your variables for NewCompanyName and stLinkCriteria to make sure they look right? This is a pretty helpful resource for using DLookUp():

                  This MSAccess tutorial explains how to use the Access DLookup function with syntax and examples. The Microsoft Access DLookup function returns a value from an Access table (or domain).


                  I also agree that you definitely need to remove the underscore in your field name and just leave the space, so I think your error is in stLinkCriteria somewhere.

                  Comment

                  Working...