Error 3021

[2Blessed4Stress]

I have a form which displays items-"Nomenclature". My subform displays the
various "lot numbers" associated with the "Nomen." On my subform I have an
'on current' code that displays "Lot Number # of #" for each Nomen. The
problem is when I get to a Nomen. that does not have any Lot Numbers, I get
the error message. This is what my code currently says:

Dim rst As DAO.Recordset
Dim IngCount As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
IngCount = .RecordCount
End With

Me.txtRecordNumber = "Lot Number" & " " & Me.CurrentRecord & " of " & " " &
IngCount

End Sub

Please give me the 1st version on how to fix this so that I don't get an
error message if there are no lot numbers or get a message saying "No Lot
Number".

 

[Steve Sanford]

I can think of two ways to handle the error.

1) check for rst.BOF and rst.EOF
2) add an error handler

Note that in both examples I commented out the line .MoveFirst. All you need
is .MoveLast to fully populate the recordset and get the recordcount.



Example 1 - check for rst.BOF and rst.EOF
'**********************
Public Sub Form_Current()
Dim rst As DAO.Recordset
Dim IngCount As Long

Set rst = Me.RecordsetClone

If Not (rst.BOF And rst.EOF) Then
With rst
' .MoveFirst
.MoveLast
IngCount = .RecordCount
End With

Me.txtRecordNumber = "Lot Number" & " " & Me.CurrentRecord & " of " &
" " & IngCount
End If
End Sub
'**********************


Example 2 - error handler
'**********************
Public Sub Form_Current()
On Error GoTo ItsBad
Dim rst As DAO.Recordset
Dim IngCount As Long

Set rst = Me.RecordsetClone

With rst
' .MoveFirst
.MoveLast
IngCount = .RecordCount
End With

Me.txtRecordNumber = "Lot Number" & " " & Me.CurrentRecord & " of " & "
" & IngCount

Exit_Here:
Exit Sub

ItsBad:

If Err.Number <> 3021 Then
Msg = "Error # " & Str(Err.Number) & " - " & Err.Description
MsgBox Msg
End If

Resume Exit_Here
End Sub
'**********************

HTH