The entire Detail section of a Microsoft Access form goes blank sometimes. The text boxes you see in design view disappear when you go to use the form, leaving nothing but empty space.
The Cause
It happens when both these conditions are met:
- There are no records to display, and
- No new records can be added.
Condition (a) can be triggered in several ways. Examples:
- The form's Data Entry property is set to Yes. (This means the form shows no existing records, i.e. it is for entering new ones only.)
- The form has a Filter applied (or is opened with a WhereCondition) that yields no records.
- The form is based on a query where the criteria yield no records.
- The form is based on a table that has no records.
Condition (b) can be also be triggered by several things:
- The form's Allow Additions property is set to No.
- The form's Recordset Type property is set to something other than Dynaset.
- The form is based on a read-only query. (If you cannot add a record directly to your query)
You can demonstrate the problem with any form, just by setting its Data Entry property to Yes, and Allow Additions to No.
If it has Form Header and Form Footer sections, controls in these sections will still be visible but they may not work properly.
Workarounds
You cannot prevent this from happening, unless you can avoid (a) and (b) being true at the same time.
If you have set Allow Additions to No so as to prevent new records, there is a simple workaround. Set Allow Additions back to Yes, so the form can show the new record when there are no others. You can prevent new records by canceling the BeforeInsert event of the form. Steps:
- Set the Before Insert property of the form to: [Event Procedure]
- Click the Build button (...) beside this. Access opens the code window.
- Insert the line Cancel = True between the other two. The code will look like this:
Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
End Sub
If you cannot prevent condition (b), you might be able to prevent condition (a). For example, if you are opening a form with a WhereCondition that could result in no records, you might prefer to display a dialog rather than the blank form. To do that, cancel the Open event of the form:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No records"
End If
End Sub
(Note: The procedure where you used OpenForm will be notified with error 2501 that the form did not open. Use error handling in that procedure to trap the message.)
There are times when you cannot prevent (a) and (b) happening together. A complex search form may have to use a read-only query, and the user's criteria may return no results. For these cases, place the criteria boxes in the form header section, and accept the fact that users will be okay if they see nothing when the search returns no results.
January 2010 update: In Access 2007 and later, check that the form's Filter On Load property is set to No so that it does not automatically apply a previously saved filter when you open the form.