vba on error problem

[Bopsgtir]

Hi all im trying to do what i thought was an easy task, but i keep getting an error.

All i want to do is on a button click open a new form and close the form im on, the form i open is for a query that could have no data, so instead of getting the run time error i just want a little message box saying no data found, can anyone tell me whats going wrong.


Option Compare Database

Private Sub GreenFilter_Click()

On Error GoTo err_NoDataFound

DoCmd.OpenForm "FrmMissingData", acNormal, , , acFormEdit, acWindowNormal
DoCmd.Close "Form1", acSavePrompt

Exit_NoDataFound:

End Sub

err_NoDataFound:
MsgBox ("No Data Found")
Resume Exit_NoDataFound

End Sub

 

[vbaInet]

So instead of opening the form, you can count how many records are in the form's Record Source and if it's zero, display a message, if it's not zero, open the form. You will use a DCount() function for this.

 

[jdraw]

It would be helpful to tell us exactly what error message you are getting.

Also, as an alternative approach, you could do a check to see if any data exists, before clicking the button

If Data exists... open Form
If No Data exists --- a message and go where you want....

You could check for data with a DCount expression or query.

 

[vbaInet]

A bit slow with the typing there jdraw

 

[Bopsgtir]

The dcount seems a good solution. I did want to expand my code aswell to do a filter and its probably the filter which would produce the zero. So can I apply a filter on the closed query then count the records

 

[jdraw]

A bit slow with the typing there jdraw

Yes, you are quick. I think I need a few more fingers !!!

 

[vbaInet]

You can also filter the query using the 3rd argument of the DCount() function.

OR

If you are getting the 2501 error, you can trap that particular error code in your error handler and display the msgbox.