Display Message Box if there are NO Records in a Form
This tip is suitable for all versions of Microsoft Access
You may or may not know that if there are no records in a report you can cancel a report from opening, by utilising the on ‘No Data’ event; but, no such event exists for a form. You can get around this though.
As an example, if you create a command button that opens a form, then the code created by Microsoft Access looks like this:
Private Sub Command7_Click()On Error GoTo Err_Command7_Click
Dim stDocName As String
stDocName = "NameOfForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command7_Click:
Exit Sub
Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub
To view this code:
Right click on the command button and select ‘properties’ - this will open the properties window;
Click on the ‘Event’ tab;
Next to the ‘On Click’ event you will notice the words [Event Procedure], place your cursor next to this event, then click on the ‘...’ button. This will open the VBE (Visual Basic Editor) window, which is where you write code. The code displayed above will be displayed.
The following bolded information will be different, depending on your particular instance, i.e. the name of your command button, the form to be opened etc.:
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim stDocName As String
stDocName = "NameOfForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command7_Click:
Exit Sub
Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub
To this code, add the following bolded code:
Note:
You will need to change "NameOfField" to the name of a field in your table, best to use a field that will have information in all records.
And "NameOfQuery" to the name of the table or query that the form you are opening is based on.
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim intHolder As Integer intHolder = DCount("NameOfField", "NameOfQuery")
If intHolder > 0 Then
stDocName = "NameOfForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria Else
MsgBox "There are no records, therefore the request has been cancelled"
End If
Exit_Command7_Click:
Exit Sub
Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click
End Sub
When finished, close the VBE window. Now when you try to open the form based on a query that returns no records, the form will not open and the user will be informed.
Wish to learn more about VBA... Click Here
Finished with 'No Records' in a form, then return to Micrrosoft Access Forms
or
