Using DCount for duplicate records

[Dkpitbull]

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"

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!!!!

[Seth Schrock]

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

[Dkpitbull]

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..

[Seth Schrock]

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.

[Dkpitbull]

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:

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!!

[Seth Schrock]

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?

[Dkpitbull]

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?

[Seth Schrock]

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

[GKJR]

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():

http://www.techonthenet.com/access/f...in/dlookup.php

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.