zero records returned

[kevcar40]

hi
i have a query that accepts an input from a user and displays the
result on a form

what i would like to do is have an error message pop up if there are
zero records reurned

is this possible?


thanks

kevin

 

[Al Campagna]

Kevcar,
Not if you are running the query manually (dbl clicking the qry name in
the Database window)
Where, and how are you initiating the query?

If you are initiating the query via code, you could always do...
IIF IsNull(DCount("[SomeFieldInYourQuery]", "qryYourQuery")) Then
Msgbox "No Records", vbOKOnly
Else
'run the query or SQL here
End If

--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[John W. Vinson]

hi
i have a query that accepts an input from a user and displays the
result on a form

what i would like to do is have an error message pop up if there are
zero records reurned

Use the Form's (cancellable) Open event:

Private Sub Form_Open(Cancel as Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry, no data for those criteria", vbOKOnly
Cancel = True
End If
End Sub

John W. Vinson [MVP]

 

[kevcar40]

Use the Form's (cancellable) Open event:

Private Sub Form_Open(Cancel as Integer)
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Sorry, no data for those criteria", vbOKOnly
Cancel = True
End If
End Sub

John W. Vinson [MVP]

Thank you very much for the information
one quick question
is it possible to have the msgbox display the warning
but stop the message "The open form action has been cancelled
i have tried using Docmd.setwarnings false

thanks

kevin

 

[Pieter Wijnen]

Add On Error Resume Next Or Specific handling of the error

ie
On Error resume Next
Docmd.OpenForm "MyForm"

HTH

Pieter

 

[John W. Vinson]

but stop the message "The open form action has been cancelled
i have tried using Docmd.setwarnings false

Trap the error message in the calling routine. Note the error number (I don't
know it offhand) and in the calling routine put code like

On Error GoTo Proc_Error
<your code to open the form>

Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case xxx <<< the "action has been cancelled" error number
Resume Next
Case Else
MsgBox "Error " & Err.Number & " in OpenForm:" & vbCrLf _
& Err.Description
End Select
Resume Proc_Exit
End Sub

John W. Vinson [MVP]