BeforeUpdate Annoying Error

[Leslie]

I created a datasheet in Access 2007 and have added code on the BeforeUpdate
for one field. I continue to get a message "Property not found" when the
code hits "End Sub". Does anyone know why this is happening and how to
prevent? Thanks in advance. Here is my code:

Private Sub ClaimNo_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_ClaimNo_BeforeUpdate
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsDUP As DAO.Recordset
Dim strSQL As String
Dim strDUPSQL As String

DoCmd.SetWarnings False

Set db = CurrentDb()

'Check to see if claim number exist.
strSQL = ("SELECT IMSLIB_CTCNK00.NKCLMN, " & _
IMSLIB_CTCNK00.NKCUST, IMSLIB_CTCNK00.NKCNAM, " & _
IMSLIB_CTCNK00.NKCDBA " & _
"FROM IMSLIB_CTCNK00 " & _
"WHERE (IMSLIB_CTCNK00.NKCLMN)=" & Me.ClaimNo & ";")

'Check to see if claim number is a duplicate.
strDUPSQL = ("SELECT tblMain.ClaimNo FROM tblMain " & _
"WHERE (tblMain.ClaimNo)=" & Me.ClaimNo & ";")

Set rs = db.OpenRecordset(strSQL)
Set rsDUP = db.OpenRecordset(strDUPSQL)

'No claim number exist.
If rs.RecordCount < 1 Then
MsgBox "Claim number is not on file. Please " & _
"verify claim number.", vbOKOnly, "Invalid Claim"
Cancel = True
Me!ClaimNo.Undo
rs.Close
Set rs = Nothing
End If

'Claim number is a duplicate.
If rsDUP.RecordCount > 0 Then
MsgBox "You have tried to enter a duplicate, " & _
"which is not allowed. " & _
vbCrLf & "Please try again.", _
vbOKOnly, "Duplicate"
Cancel = True
Me!ClaimNo.Undo
rsDUP.Close
Set rsDUP = Nothing
Else
Me.Customer = rs!NKCUST
If rs!NKCNAM = "" Then
Me.InsdName = rs!NKCDBA
Else
Me.InsdName = rs!NKCNAM
End If
End If

Exit_ClaimNo_BeforeUpdate:
Exit Sub

Err_ClaimNo_BeforeUpdate:
Call LogError(Err.Number, Err.Description, "frmSubLog -
ClaimNo_BeforeUpdate()")
MsgBox prompt:="Error Number: " & Err.Number & vbCrLf _
& Err.Description, _
buttons:=vbCritical + vbMsgBoxHelpButton, _
Title:="Error!", _
HelpFile:=Err.HelpFile, _
context:=Err.HelpContext
Resume Exit_ClaimNo_BeforeUpdate

End Sub

 

[Leslie]

I did figure this out by using a dlookup and moving it the afterupdate. I
replaced the sql stmt w/dlookup to send the user a msg they could understand
when claimno entered is a duplicate. I set claimno to unique (no duplicates)

 

[Jim Burke in Novi]

I do see a flaw in your original code that would cause an error when
rs.RecordCount < 1 is true. In that case you close rs and set it to nothing,
but don't exit the sub. So you would then drop down to the next If. And in
that one, if rsDUP.RecordCount is not >0, you're attempting to reference
fields from rs, which was closed and set to nothng. That may have been your
problem.