Error 3021

[Guest]

I'm running filters on a form in Access to show selected data using the
following code:-

Function MeetingForm_chkCancelled()
'On Error GoTo MeetingForm_chkCancelled_err'

Dim rstCanc As DAO.Recordset, rstCancFilt As DAO.Recordset

Set db = CurrentDb
Set rstCanc = db.OpenRecordset("Meeting Details Table", DB_OPEN_DYNASET)
rstCanc.MoveFirst
rstCanc.Filter = "[Status] = 2"
Set rstCancFilt = rstCanc.OpenRecordset()
rstCancFilt.MoveFirst

[Forms]![Meeting Form]![chkAtWork] = False
[Forms]![Meeting Form]![chkOpen] = False
[Forms]![Meeting Form]![chkAllMtgs] = False
[Forms]![Meeting Form]![chkClosed] = False
[Forms]![Meeting Form]![chkCancelled] = True

rstCancFilt.Close
rstCanc.Close

[Forms]![Meeting Form].Filter = "[Status] = 2"
[Forms]![Meeting Form].FilterOn = True

Exit Function

MeetingForm_chkCancelled_err:
If Err.Number = 3021 Then
MsgBox "There are no cancelled meetings currently recorded on the
system.", vbOKOnly, "No Records"
[Forms]![Meeting Form]![chkCancelled] = False
Exit Function
Else
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If

End Function

There are cuurently no status 2 meetings in my table, so I would expect to
get the error message "There are no cancelled meetings..." etc etc. The code
is sticking at the line rstCancFilt.MoveFirst.

I use a similar piece of code elsewhere in the dbase:-

Function frmLeaderForm_chkTrainees()
On Error GoTo frmLeaderForm_chkTrainees_err

Dim rstCanc As DAO.Recordset, rstCancFilt As DAO.Recordset

Set db = CurrentDb
Set rstCanc = db.OpenRecordset("Address Book Table", DB_OPEN_DYNASET)
rstCanc.MoveFirst
rstCanc.Filter = "[Status] = 5"
Set rstCancFilt = rstCanc.OpenRecordset()
rstCancFilt.MoveFirst

[Forms]![Leader Form - New]![chkResting] = False
[Forms]![Leader Form - New]![chkActive] = False
[Forms]![Leader Form - New]![chkAllLdrs] = False
[Forms]![Leader Form - New]![chkResign] = False
[Forms]![Leader Form - New]![chkTrainees] = True

rstCancFilt.Close
rstCanc.Close

[Forms]![Leader Form - New].Filter = "[Status] = 5"
[Forms]![Leader Form - New].FilterOn = True

Exit Function

frmLeaderForm_chkTrainees_err:
If Err.Number = 3021 Then
MsgBox "There are no trainees currently recorded on the system.",
vbOKOnly, "No Records"
[Forms]![Leader Form - New]![chkTrainees] = False
Exit Function
Else
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If

End Function

This one is working fine, there are no status 5 Leaders in the database, so
I get the error message as expected. Help!

 

[Allen Browne]

You cannot MoveFirst when there is no record to move to.
Test RecordCount. It will be at least 1 if there are records:
Set rstCancFilt = rstCanc.OpenRecordset()
If rstCancFilt.RecordCount > 0 Then
rstCancFilt.MoveFirst
...

If the goal is simply to remove the filter again if it yields no records,
why not try:
Me.Filter = "Status = 2"
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Oops: none!"
Me.FilterOn = False
End If

If you really want to peek before filtering, you could use DLookup() on the
primary key:
strWhere = "Status = 2"
If IsNull(DLookup("ID", "Table1", strWhere)) Then
MsgBox "None!"
Else
Me.Filter = strWhere
Me.FilterOn = True
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm running filters on a form in Access to show selected data using the
following code:-

Function MeetingForm_chkCancelled()
'On Error GoTo MeetingForm_chkCancelled_err'

Dim rstCanc As DAO.Recordset, rstCancFilt As DAO.Recordset

Set db = CurrentDb
Set rstCanc = db.OpenRecordset("Meeting Details Table",
DB_OPEN_DYNASET)
rstCanc.MoveFirst
rstCanc.Filter = "[Status] = 2"
Set rstCancFilt = rstCanc.OpenRecordset()
rstCancFilt.MoveFirst

[Forms]![Meeting Form]![chkAtWork] = False
[Forms]![Meeting Form]![chkOpen] = False
[Forms]![Meeting Form]![chkAllMtgs] = False
[Forms]![Meeting Form]![chkClosed] = False
[Forms]![Meeting Form]![chkCancelled] = True

rstCancFilt.Close
rstCanc.Close

[Forms]![Meeting Form].Filter = "[Status] = 2"
[Forms]![Meeting Form].FilterOn = True

Exit Function

MeetingForm_chkCancelled_err:
If Err.Number = 3021 Then
MsgBox "There are no cancelled meetings currently recorded on the
system.", vbOKOnly, "No Records"
[Forms]![Meeting Form]![chkCancelled] = False
Exit Function
Else
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If

End Function

There are cuurently no status 2 meetings in my table, so I would expect to
get the error message "There are no cancelled meetings..." etc etc. The
code
is sticking at the line rstCancFilt.MoveFirst.

I use a similar piece of code elsewhere in the dbase:-

Function frmLeaderForm_chkTrainees()
On Error GoTo frmLeaderForm_chkTrainees_err

Dim rstCanc As DAO.Recordset, rstCancFilt As DAO.Recordset

Set db = CurrentDb
Set rstCanc = db.OpenRecordset("Address Book Table", DB_OPEN_DYNASET)
rstCanc.MoveFirst
rstCanc.Filter = "[Status] = 5"
Set rstCancFilt = rstCanc.OpenRecordset()
rstCancFilt.MoveFirst

[Forms]![Leader Form - New]![chkResting] = False
[Forms]![Leader Form - New]![chkActive] = False
[Forms]![Leader Form - New]![chkAllLdrs] = False
[Forms]![Leader Form - New]![chkResign] = False
[Forms]![Leader Form - New]![chkTrainees] = True

rstCancFilt.Close
rstCanc.Close

[Forms]![Leader Form - New].Filter = "[Status] = 5"
[Forms]![Leader Form - New].FilterOn = True

Exit Function

frmLeaderForm_chkTrainees_err:
If Err.Number = 3021 Then
MsgBox "There are no trainees currently recorded on the system.",
vbOKOnly, "No Records"
[Forms]![Leader Form - New]![chkTrainees] = False
Exit Function
Else
MsgBox Err.Number & " - " & Err.Description
Exit Function
End If

End Function

This one is working fine, there are no status 5 Leaders in the database,
so
I get the error message as expected. Help!