Access 2003 clears all controls on form when recordset query returns null

[mjkerr]

I think the subject says it all. I use 5 buttons to switch the main
form's recordset to different queries for navigation/assignment of
projects. When the query returns records, all is well. However, when I
set the recordset to a query that returns no records (say, complete
reviews when there are none), the form goes blank, and all controls are
lost. I'd rather not run the query and check the recordset for values,
so does anybody have a better idea?

 

[Allen Browne]

The Detail section of the form goes completely blank when:
a) there are no records to display, and
b) no new record can be added.

If (b) is due to the form's AllowAdditions property being set to No,
changing it to Yes will solve the problem. (You can prevent additions by
cancelling the form's BeforeInsert event.)

If (b) comes from the fact that the source query is read-only, you could
test for that state and restore previous RecordSource:
Private Sub cmd1_Click()
Dim strSql As String
strSql = Me.RecordSource
Me.RecordSource = "Query5"
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No matches"
Me.RecordSource = strSql
End If
End Sub

If you want to test the result before you assign Query5, you could DLookup()
a required field (such as a primary key.) DLookup() will return Null if
there are no records:
Private Sub cmd1_Click()
If IsNull("ID", "Query5") Then
Msgbox "No matches"
Else
Me.RecordSource = "Query5"
End If
End Sub

 

[mjkerr]

Thanks for the quick reply. After plinking around with it, I settled on
an on error clause. An error is being thrown, just not being displayed.

If this presents problems, I'll try to incorporate your solution,
thanks!