Solved Checking for duplicate data on a form against data in a query (1 Viewer)

[Benginner2212]

I am working on a database that will be used to keep track of various cable that are used to connect equipment. The cables are broken down in to cable categories and then by cable numbers. And I need to avoid duplicate cable numbers.

I have a query that is based of a table called tblCableInfomation and only uses the fields cableCategory_PK, which is formatted as a number and the field CableNumber which is a string format. I am trying to use a dcount function to count the number of records in the query, but when I run my code I keep getting a mismatched data error and I can't see what I am doing wrong.

This is my code:

Private Sub CableNumber_AfterUpdate()
    If DCount("CableNumber", "qryCableNumberCheck", "cableCategory_PK=" & Me.cboCableCategory & "" And "CableNumber='" & Me.CableNumber & "'") > 0 Then
        Me.CableNumber.Value = Null
        MsgBox "The Loop Worked", vbOKOnly, "Cable Number Verification"
        Me.cboCableCategory.SetFocus
    Else
        MsgBox "The Loop Didn't Work", vbOKOnly, "Try Again"
    End If
        
End Sub

TIA

 

[theDBguy]

Try it this way:

"cableCategory_PK=" & Me.cboCableCategory & "And CableNumber='" & Me.CableNumber & "'")

 

[Benginner2212]

Thank you. That solved the problem.

What mistake was I making with the " and ' in my original code?

 

[theDBguy]

Thank you. That solved the problem.

What mistake was I making with the " and ' in my original code?

You just had extra double quotes that messed up the meaning of the expression.

Good luck with your project!

 

[Pat Hartman]

When you are building a string within a string, it is ALWAYS easier to build the string into a variable. That allows you to easily see what your build instruction built.

Dim strWhere As String

strWhere = "cableCategory_PK=" & Me.cboCableCategory & "" And "CableNumber='" & Me.CableNumber & "'"
If DCount("CableNumber", "qryCableNumberCheck", strWhere) > 0 Then

Then you can put a stop in the "If" line and view the value of strWhere in the immediate window. If the code even compiles, which it may not, you will see the error.

@theDBguy showed you what the expression should look like. My code shows the erroneous expression to show you how to debug these things easily.

 

[Benginner2212]

Thank you, I will start doing that because I always run into these kid of issues when I am trying to write a function using multiple criteria.

 

[Gasman]

I would always add a debug.print of the string variable.