Error 3420

[SHIPP]

I am creating a recordset from an SQL statement. When
there are not any records I receive Error 3420. I have
tried to use On Error Resume Next but it still stops.
Otherwise the subroutine works great. Any ideas on how to
check to see if a recordset exists. The following is the
code:

' Selects distinct records by a combination of Entry
No. and Date
strSQL = "SELECT DISTINCT
tblProjectionHistory.ProjKey " _
& "FROM tblProjectionHistory " _
& "ORDER BY tblProjectionHistory.ProjKey;"

Set rsEntRS = dbMyDB.OpenRecordset(strSQL,
dbOpenDynaset)

' Create records for tmpProjection
Do While Not rsEntRS.EOF
LetProjKey (rsEntRS("ProjKey"))
DoCmd.SetWarnings False
' Create a temporary table by entry no. and date
DoCmd.OpenQuery "qmaktmpProjection", acViewNormal,
acEdit
DoCmd.SetWarnings True
' Calculates Day Fee vs Entry Fee
Call CalcProjFees
rsEntRS.MoveNext
Loop

ExitHere:
dbMyDB.Close
Set dbMyDB = Nothing

***************************************************
* THE ERROR OCCURS AT rsEntRS.Close
* When there are not any records in the recordset.
***************************************************

rsEntRS.Close
Set rsEntRS = Nothing
Exit Sub

HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error " & Err.Number & ": " &
Err.Description, vbCritical, strProcName
End Select
GoTo ExitHere

End Sub

 

[Allen Browne]

You don't show where dbMyDB is set, but rsEndRS clearly depends on it.
Therefore it makes sense that if you destroy dbMyDB, Access will have
destroyed rsEndRS as well.

Reverse the order of the code:
rsEntRS.Close
Set rsEntRS = Nothing
dbMyDB.Close
Set dbMyDB = Nothing

If dbMyDB is set to an external database with an OpenDatabase() then, yes,
you should close what you opened. However, if dbMyDB is set to the currently
open database, e.g.:
Set dbMyDB = dbEngine(0)(0)
you did not open it and you should not close it. Either way, do set the
variable to Nothing.