Report No data found

[Lyncroft]

Common question this and I've searched the archives but cant find the solution. Using the QBF to print reports with the following:

Private Sub cmdrunQry_Click()
Dim db As Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

where = Null
where = where & " AND [Council]= '" + Me![Council] + "'"
where = where & " AND [Region]= '" + Me![Region] + "'"
where = where & " AND [Status]= '" + Me![Status] + "'"
where = where & " AND [StatusRevoked]= '" + Me![StatusRevoked] + "'"

Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblCert " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "rptList", acViewPreview, "qryDynamic_QBF"
DoCmd.ShowToolbar "MyToolBarReports", acToolbarYes
DoCmd.ShowToolbar "Print Preview", acToolbarNo


End Sub

How do I trap the "No records found" error message. I know theres code to trap it (I found it on the board) but I dont know how to place it within the above code.

Thanks

 

[Fizzio]

Use the report's On No Data event to process what you want.

 

[Lyncroft]

Hi Fizzio

I've done that using the "NoData" event. I've put in a message, "records found" but when you click OK to cancel the message you get that error 2501 message come up. I know somewhere within the above code I have to trap error 2501 just don't know how

 

[Fizzio]

Private Sub cmdrunQry_Click()
Dim db As Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb

On Error goto ErrHandler
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

where = Null
where = where & " AND [Council]= '" + Me![Council] + "'"
where = where & " AND [Region]= '" + Me![Region] + "'"
where = where & " AND [Status]= '" + Me![Status] + "'"
where = where & " AND [StatusRevoked]= '" + Me![StatusRevoked] + "'"

Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblCert " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "rptList", acViewPreview, "qryDynamic_QBF"
DoCmd.ShowToolbar "MyToolBarReports", acToolbarYes
DoCmd.ShowToolbar "Print Preview", acToolbarNo

ExitCode:
exit Sub

ErrHandler:

Select case err.number

Case 2501
resume exitcode

case else
resume next 'but this is not ideal. It is better to trap errors rather than ignoring them!

end select
End Sub

 

[Lyncroft]

Thanks for that Fizzo but I'm still getting the 2501 message. Triple checked it too!!

 

[Lyncroft]

Success - I took out the GoTo 0 bit. Not sure I understand this but it gets the job done. Many thanks again Fizzio.

 

[Fizzio]

I did not spot the on error goto 0 !

Essentially the on error is the error handling bit

On error goto 0 means if there is an error goto line 0 in the code (first line)

On error resume next - ignore the error and bash on regardless - hence not very good!

on error goto ErrHandler - go to specific part in the code called ErrHandler and resume execution there.

You should only have 1 On Error ....... call in the code to trap errors. Do you follow the select case bit OK?

 

[Lyncroft]

Yeah just about there. I'll have to sit down with a clear head tomorrow and have another look.

You should do this as a job (but perhaps physio is more fun!!!!???). By the way just at the back of my hamstring I've got a tender spot. Doesn't hurt when I run and doesn't pull when I stretch or jump. Only hurts when I sit down really. Like a dull ache all day. Do you think rest is just the answer. Play a lot of football. Bit annoying.

Thanks again for your patience

 

[Fizzio]

I enjoy the Physio (especially the painful bits) but it is always useful to broaden the horizons - I have aspirations to one day be able to get to grips with ASP and Access / SQL but that is out of my league at the minute!
Re: The Hamstring. If there was no history of trauma to the muscle then it is unlikely to be that. It is much more likely to be referred pain from your back (do you get back pain?), irritating the nervous tissue (not meaning that it gets a fright easily ), causing a dull pain in the leg. Does it ease in standing / walking?
Initially, I suggest improving your sitting posture - put a cushion behind your lower back and envisage sitting up tall with your buttocks right to the back of the chair. See if this helps and get someone to have a look at your back.
I think I'll move into e-physio (It's bound to pay more than the NHS!)

 

[Lyncroft]

Thanks Fizzio. I'll follow your advice. Hope you don't think I was taking advantage of your very helpful character but it seemed too good an opportunity to miss!!!!

Cheers again