Display Message Box if there are NO Records in a Form

This tip is suitable for all versions of Microsoft Access

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