DCount() problem

[astarbyfar]

Im having a problem with the following code:

Private Sub PSM_Code_BeforeUpdate(Cancel As Integer)
Dim PSM As String

PSM = Me.PSM_Code.Value

'Check Part table for duplicate PSM Codes
If DCount("PSM_Code", "Part", "[PSM_Code]=" & "'" & PSM & "'") > 0 Then
'Message box warning of duplication
If MsgBox("Warning PSM Code " & PSM & " has already been entered.", vbOKOnly, "Duplicate Primary keys") = vbOK Then Cancel = True
End If
End Sub

Everything works and it picks up the duplicate. However when I re-enter a PSM code that isnt a duplicate it still gives me a message. Anyone got any ideas?

 

[FoFa]

Have you run a debug/watch/display on your Me.PSM_Code.Value to verify it is changing?

 

[astarbyfar]

Sorry its taken so long to get back but Ive had a problem with my internet. Anyway, Yes the code is changing each time (displayed through the msgbox) but Im still getting the error message. any ideas?

 

[ghudson]

Try this [I added a space after the "[PSM_Code]="]...

If DCount("PSM_Code", "Part", "[PSM_Code]= " & "'" & PSM & "'") > 0 Then
'Message box warning of duplication
     If MsgBox("Warning PSM Code " & PSM & " has already been entered.", vbOKOnly, "Duplicate Primary keys") = vbOK Then Cancel = True
Else
     msgbox "not a duplicate'
End If

Sometimes I have had to use a differnt field to display that is not a part of my criteria. If the above does not work, try using a different field instead of DCount("PSM_Code", try DCount("AnotherField",