Using DCount to check for exisitng records

[dylcon]

I have a form that asks for the user to input a NEW customer name. I want to check the existing customers to see if the input already exists.



So far I have:

Private Sub CustomerName_AfterUpdate()    Companychk = DCount([Me.CustomerName], "tblCustomer", "[CustomerName]")
    If Companychk > 0 Then
        MsgBox ("There is already a customer with this name. You cannot create this a new customer")
    End If
            
End Sub

CustomerName is my textbox that is being filled with the information.

[pbaldy]

See here for correct syntax:

DLookup Usage Samples

[dylcon]

So now I have:

    Companychk = DCount("[CustomerName]", "tblCustomer", "Criteria = '" & Forms!NewCustomerForm!CustomerName & "'")

but it still does not seem to be working. I keep getting an error.

[pbaldy]

Criteria would be replaced by the name of the field being tested.

[dunc723]

Try

Companychk = DCount("CustomerName", "tblCustomer", "CustomerName = "' & Forms!NewCustomerForm!CustomerName & "'")

You don't need the Criteria = statement.

[pbaldy]

Try

Companychk = DCount("CustomerName", "tblCustomer", "CustomerName = "' & Forms!NewCustomerForm!CustomerName & "'")

You don't need the Criteria = statement.

Not sure what you mean by not needing the criteria statement, since you included it too. In any case, your quotes are wrong.

[dunc723]

Sorry, I see I reversed " and '. Good catch.

I meant in the criteria field of the function, the poster had "Criteria = '" etc. which as you obviously know is not the syntax the function wants. Unfortunately you did not even try to provide a good example, so I did.

[Missinglinq]

If DCount("*", "tblCustomer", "CustomerName = '" &  Me.CustomerName & "'") Then
   MsgBox "There is already a customer with this name. You cannot create this a new customer"
End If

Linq ;0)>

[pbaldy]

I meant in the criteria field of the function, the poster had "Criteria = '" etc. which as you obviously know is not the syntax the function wants. Unfortunately you did not even try to provide a good example, so I did.

The syntax was fine, they just needed to replace the word Criteria with the actual field name. As to good examples, I provided a link full of them.

[dylcon]

Thank you everyone for your contributions. I just learned about changing the field on the table to not allow duplicates. As far as that goes, would you suggest that method, or using the DCount method?

I would prefer to use DCount so I control the error message instead of something ambiguous that will confuse the future users of the form.

[pbaldy]

I often do both. The table setting makes sure it can't happen, but testing lets you warn the user earlier and as you say with more control.

[Dal Jeanis]

dylcon - you may occasionally have to sweep your database for duplicates caused by mis-spellings anyway, so keep that in mind in your overall design. As db admin, you'll want to think that through in advance, and provide yourself with easy tools.

[dylcon]

If DCount("*", "tblCustomer", "CustomerName = '" & Me.CustomerName & "'") Then MsgBox "There is already a customer with this name. You cannot create this a new customer"

End If

Hey Linq, what exactly does all that mean? I am trying to use this same method again, but this time I am validating that two different fields in the same record doesn't exist somewhere else.

[Dal Jeanis]

dylcon - if you make a query MyQuery joining your tables before you use DCount, the query name MyQuery replaces tblCustomer in the second slot, then your compound condition, without the word WHERE, goes in the third slot. Voila.

[dylcon]

When you say "slots", do you mean the different portions of a DCount expression, or do you mean the portions within a query design? Sorry, I have been getting used to some other stuff, but I really don't know much about queries whatsoever.