query results in form, but what if query is null?

[ebodin]

I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)

I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?

Thanks for any help.

 

[ScottGem]

use something like this:

If Dcount("[keyfield]","queryname") > 0 Then
DoCmd.OpenForm...
Else
DoCmd.OpenForms...
End If

 

[ebodin]

I didn't mention that the query runs from a command button and the query asks for user input. How/where would I include the If statement in this code?

Private Sub cmd_OpenShopFloorAccess_Click()

On Error GoTo Err_cmd_OpenShopFloorAccess_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrm_AllChemShopFloorQry"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmd_OpenShopFloorAccess_Click:
Exit Sub

Err_cmd_OpenShopFloorAccess_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenShopFloorAccess_Click

End Sub

 

[ScottGem]

Instead of the OpenForm line of code in your example

 

[ebodin]

You mean like this?

Private Sub cmd_OpenShopFloorAccess_Click()

On Error GoTo Err_cmd_OpenShopFloorAccess_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrm_AllChemShopFloorQry"
If DCount("[e-MSDS]", "qry_AllChemShopFloor") > 0 Then
DoCmd.OpenForm sfrm_AllChemShopFloorQry
Else
DoCmd.OpenForm sfrm_NoQryResults
End If

Exit_cmd_OpenShopFloorAccess_Click:
Exit Sub

Err_cmd_OpenShopFloorAccess_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenShopFloorAccess_Click

End Sub


I get a "you cancelled previous operation" message when I do this.

Thanks for your help.

 

[ScottGem]

Try:
If Nz(DCount("[e-MSDS]", "qry_AllChemShopFloor"),0) > 0 Then