DCount() not counting?

[sleptema]

I have a button that users can add customers. When they click an inputbox records their result, and if the customer doesn't exist, it adds it to tblCustomer, if it does, it brings a warning message up. The problem is it always rejects the input whether it is a customer or not. The insert into command works fine, so it cannot be that.



Any ideas?

Private Sub cmdNewCustomer_Click()
    Dim NewCustomer As String
    Dim StrSQL As String
    
AddCustomer:
        NewCustomer = InputBox("What is the new customers name?", "Add Customer")
        
        If NewCustomer = "" Then
        Exit Sub
        ElseIf DCount("Customer", "tblCustomer", "' & NewCustomer & '") > 0 Then
        MsgBox "That customer already exists!", vbInformation, "Duplicates Detected"
        GoTo AddCustomer
        End If
           
        DoCmd.OpenTable "tblCustomer", acViewNormal, acAdd
        DoCmd.GoToRecord acDataTable, "tblCustomer", acNewRec
        StrSQL = "INSERT INTO tblCustomer (Customer) VALUES ('" & NewCustomer & "')"
        CurrentDb.Execute StrSQL, dbfailonerror
        DoCmd.Close acTable, "tblCustomer", acSaveYes
        cboCustomer.Requery
        
        MsgBox "New customer recorded!", vbInformation, "Entry Successful"
        
End Sub

[RuralGuy]

Your DCount() syntax needs adjustment in the last argument.
http://access.mvps.org/access/general/gen0018.htm

[sleptema]

You may need to elaborate on that further please.

For strings: (note the apostrophe before and after the value)
DLookup("FieldName" , "TableName" , "Criteria= 'string'")

The field within the table is "Customer"
The table name is "tblCustomer"
The criteria I am searching for is the string that the inputbox output, which in debug mode, shows the value I want.

I can't see what I am doing wrong.

[June7]

The criteria argument needs to be an expression: "somefield=somevalue"

DCount("Customer", "tblCustomer", "Customer='" & NewCustomer & "'")

[sleptema]

The criteria argument needs to be an expression: "somefield=somevalue"

DCount("Customer", "tblCustomer", "Customer='" & NewCustomer & "'")

God damnit! I was so close. Anyway you were bang on the money, works a charm now. Thanks so much. Stuck on that issue for a long time haha.

(obviously I am no VBA genius hahaha)