Checking Existing Records for duplicates (beforeupdate)

[ianward]

Hi all,

I have been trying to adapt a piece of code I found on this forum some while ago which checks a fields value before updating to check for duplicates for both new and existing records. The first part of the code works fine for new records.

It is the second part which i am struggling with if a user updates an existing record, where i get the following error:

Run-time error '3075'
Syntax error (missing operator) in query expression
'[Organisation Code]='5CC' AND [Organisation Code]<>5CC'

I have posted the onevent code below and would be very gratefull if someone can point me in the right direction, i get the feeling it is staring me in the face... but its now been staring at me for hours and i still cant find it!

Private Sub txt_OrganisationCode_BeforeUpdate(Cancel As Integer)
 ' Validate
    ' Check for Duplicates in 1) New Record and 2) Existing Record

            '1) New Record
            If Me.NewRecord Then
            strSQL = "[Organisation Code]='" & Me!txt_OrganisationCode & "'"
                If DCount("[Organisation Code]", "[tbl_Organisations_And_Sites]", strSQL) > 0 Then
                    MsgBox "This Provider ID has already been allocated" & vbCrLf & _
                    "Click 'OK' then press 'Esc' to cancel the text" & vbCrLf & "Then choose another ID to use for this provider" _
                    , vbExclamation, "Organisation Code Validation"
                    Cancel = True
                End If
            Else
                '2) Existing Record
                strSQL = "[Organisation Code]='" & Me!txt_OrganisationCode & "' AND [Organisation Code] <>" & Me![Organisation Code]
                If DCount("[Organisation Code]", "[tbl_Organisations_And_Sites]", strSQL) > 0 Then
                    MsgBox "This Provider ID has already been allocated" & vbCrLf & _
                    "Click 'OK' then press 'Esc' to cancel the text" & vbCrLf & "Then choose another ID to use for this provider" _
                    , vbExclamation, "Organisation Code Validation"
                    Cancel = True
                End If
            End If
End Sub

Many Thanks - Ian

 

[DCrake]

'[Organisation Code]='5CC' AND [Organisation Code]<>5CC'

Can you spot the mistake ... No quotes around the second condition.

strSQL = "[Organisation Code]='" & Me!txt_OrganisationCode & "' AND [Organisation Code] <>'" & Me![Organisation Code] & "'"

Also this criteria defies logic


code = X and code <> X is ambiguous

 

[ianward]

Thanks DCrake, conflicting/ambiguous criteria...

I think today is one of those days where I cant see the wood for the trees

I have removed the second '<>....' and it now appears to be working as it should

Thanks again - Ian