You need to tell Access what do to with the cancel like this:
If Answer = vbCancel Then Exit Sub
Cancel = True
Here is the whole Sub if you want to see how I did it in my app. I have been told that the way I do this is not efficient but it works well for me and I have never had any errors with the procedure.
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim Answer As Variant
If DCount("[jobname]", "JobNumbers", "[JobNumber]= '" _
& Me!JobNumber & "'") = 0 Then
strMsg = "Please try a different number"
strTitle = "Invalid Job Number"
intStyle = vbOKCancel + vbInformation
Answer = MsgBox(strMsg, intStyle, strTitle)
If Answer = vbCancel Then Exit Sub
Cancel = True
End If