|
You may or may not know that 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(or if you receive text email the information surrounded by a *):
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.
Do you like this tip? Subscribe to my weekly Newsletter to receive tips
weekly via email.
Click Here to subscribe.
|
