no data event report

[sven2]

Hello,

for the first time I am working with an BE SQL server and now I have the following problem.

I want to cancel the command cmd.openreport when there is no data.
Normaly i use on the report --> cancel = true

And behind the button the following code:

Private Sub cmdPrint_Click()

On Error GoTo Err_cmdPrint_click

Dim stDocName As String
stDocName = "Rpt_Moederrollenoverzicht"
DoCmd.OpenReport stDocName, acViewPreview

Exit_cmdPrint_click:
Exit Sub

Err_cmdPrint_click:
If Err.Number = 2501 Then
Resume Next
Else
MsgBox Err.Description
Resume Exit_cmdPrint_click
End If

End Sub

This is not working anymore and I don't know why?

Who can help me?

Thanks in advance,
Sven.

 

[pbaldy]

Define "not working". I use SQL Server back ends most of the time, and that technique should still work.

 

[sven2]

Hello,

I still get the error 2501 and if I put a msgbox after the line
If Err.Number = 2501 Then
msgbox "2501"

then the msgbox is not appearing

So, I get an error 2501 but the errorhandler is not catching this error ??

 

[pbaldy]

Sorry for the stupid question, but are you sure there's no data, as opposed to a blank record or something? My normal error trap looks like this and works fine:

  Select Case Err
    Case 2501 
      MsgBox "No data to display"
...

 

[sven2]

Hello,

on the report no data event I have the following code:
Dim Lresponse As Integer
Cancel = True
Lresponse = MsgBox("Met de opgegeven parameters is er geen data gevonden!" & _
vbCrLf & "Het rapport wordt hiermee afgesloten.", vbOKOnly Or vbExclamation, "Rapport")

this msgbox is displayed on the screen and after that I get the error 2501.
So there is no data ...
Also when I do it like your code with a select case the error is still appearing.

 

[pbaldy]

I'm running out of thoughts. Can you post a sample db?

 

[boblarson]

Set a breakpoint at the point where you are opening your report and step through and actually see where it is happening.

 

[datAdrenaline]

Your code is syntactically correct ... I would suggest you do a couple of things ...

Perform a Compact and Repair operation. If no joy, then, Verify your VBA settings for error handling ...

In the VBA editor: Tools> Options > General Tab > Error Handling ... verify the selection is appropriate (typically Break on Unhandled Errors)

In addition, verify your Allow Special Keys option. In the Access UI: Tools > StartUp > Allow Special Keys ... during development, this should be checked. I actually ALWAYS keep it checked.

If Allow Special Keys is NOT selected in Access and Break on All Errors is selected in VBA, then your code will just stop when an error is encountered ... no msg, no error handler .. nothing ... just plain stop.

If messing with those settings does not yeild success then, I would suggest you "Decompile" your app ... to do that launch your app from a short cut or the "Run" box with a command line like this:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /excl /decompile "C:\Folder\MyDb.MDB\MxPv2_Dev1.mdb"

When the db opens (note: there is NO message that says "Hey - Your decompiled now!"), go into the VBA editor are compile the app ... then try your report again.

If THAT does not work, I would suggest that you create a NEW BLANK database and IMPORT all of your objects into it. By doing that, you can fix some partial corruption and recompile your app.

Hope all this helps!

 

[pbaldy]

Where's the whacking-my-head icon? My money's on Brent's second thought; I often forget about that one.

 

[sven2]

Hello,

good news ... very good news
the second thought did the trick:

In the VBA editor: Tools> Options > General Tab > Error Handling ... verify the selection is appropriate (typically Break on Unhandled Errors)

I would never solved this problem alone ... thank you very much!!

Best regards,
Sven.

 

[datAdrenaline]

Excellent! ... glad to hear you got it sorted!