Solved Checking for duplicate data on a form against data in a query

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

 

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