Error event when running report based on parameter query

[jaydeebe]

I use a custom dialouge box to take two parameters (postcode & storename) from a user to run a query report. It works fine, handels null responses and wildcard input (on the postcode), but for one thing. When a user puts in a postcode that doesn't exist (ie. there is no store of that name with a postcode like that) I get an error message 2427 you entered an expression without a value. Clicking debug highlights the first line of my page break counter for the report. As long as the postcode or partial postcode, provided by the user, exists for any given store then the report runs fine and the pagebreakes are in the correct place.

I have tried adding code to the error event of the report, but it doesn't work. Any ideas anyone?

Private Sub Report_Error(DataErr As Integer, Response As Integer)
MsgBox "There is no store with that postcode! Please try again!", vbOKOnly, "Error"
DoCmd.OpenForm "frmFindNear", acNormal, , , acFormEdit
End Sub

Many thanks


Jaydeebe BSC (hons) MCP

 

[jaydeebe]

Can anyone help, please. This is the only bug in the way of finishing this project.

Jaydeebe BSC (hons) MCP

 

[CosmoKramer]

jaydeebe,

Try your code in your report's On No Data event instead...

Hoc nomen meum verum non est.

 

[jaydeebe]

Thanks very much for your reply. I have done as you suggested and now get the message box to appear correctly but on clicking ok the same error occurs as previously.

Jaydeebe

 

[jaydeebe]

OK, I've done it. This code starts my report from a button click on my custom dialogue box (where two parameters are entered by the user).

Private Sub cmdPreTradePost_Click()
On Error GoTo Err_cmdQuit_Click

DoCmd.OpenReport "rptPreTradePost", acPreview
DoCmd.Close acForm, "frmfindnear"

Exit_cmdQuit_Click:
Exit Sub

Err_cmdQuit_Click:
DoCmd.OpenForm "frmInvalid", acNormal
Resume Exit_cmdQuit_Click
End Sub

frmInvalid is another custom dialogue box that I use instead of a standard msgbox. The ok button closes the frmInvalid dialogue box and opens the original dialogue frmfindnear for the user to re-input the parameters.

The code for the Nodata property of the report is just

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub


Jaydeebe