zero records returned

  • Thread starter Thread starter kevcar40
  • Start date Start date
K

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
 
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."
 
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
Click to expand...

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]
 
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]
Click to expand...

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
 
Add On Error Resume Next Or Specific handling of the error

ie
On Error resume Next
Docmd.OpenForm "MyForm"

HTH

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

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]
 
Back
Top