#Error on calling blank report 1

[ChrisTheAncient]

Working in Access XP - but will also need to save in A97.

In a working form, I successfully call up a print of a report on some sub-data on that form. All works well using the following...

Private Sub PrintTestDetails_Click()

Me.Refresh

Dim stDocName As String

stDocName = "ReportOnTestResultsByCustomer_Form"

DoCmd.OpenReport stDocName, acPreview, , "[CustomerNumber]=forms!ActiveCustomers!CustomerNumber"

End Sub

BTW, that last DoCmd is all one line!


If, however, that subset of data is empty, all I get is an 'empty' form with inappropriate #Error in places.


If I set an On No Data event on that called up report, using...

Private Sub Report_NoData(Cancel As Integer)

MsgBox "This report contains no data! Cancelling..."

Cancel = True

End Sub

I get my nice message box. But...

I then get an error message and the earlier DoCmd.OpenReport stDocName, highlighted as the buggy line. Unfortunately, in my attempts to cure the error message and try other things, I can't remember what that error message was!

I sincerely hope that makes sense!


Is there a way of cancelling a report (by event programming) if it is empty when it is called up from a form?

TIA

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[RoyVidar]

Hi!

If what you provided is excact the same code you're running, the "all on one line" docmd line might be the problem, cause it would need the value from the form control, in stead of the reference in the criteria string, something like this if it's numeric:

[tt] DoCmd.OpenReport stDocName, acPreview, , "[CustomerNumber]=" & forms!ActiveCustomers!CustomerNumber[/tt]

HTH Roy-Vidar

 

[ChrisTheAncient]

Hi Roy

I'm a little lost (but I am stoopid!)

I can't see any difference in your line!

The code always works fine until I have an empty report called up. I could live with it, but it's a teensy weensy bit of a hassle.

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[RoyVidar]

Hi again!

In my line, the double quotes stops after the fieldname part of the criteria ("[CustomerNumber]=&quot, wilst the rest of the line is "unquoted", which should ensure that the value from the form reference is added to the criteria. In your line the whole criteria part is quoted.

Post back, if unclear

HTH Roy-Vidar

 

[ChrisTheAncient]

Hi again Roy

Now I see your difference! I must get my reading glasses (binoculars) checked!

Tried that...

Even the 'working' records now come up with a #Error now!

I did try and have a go (by myself) with all sorts of experiments on this some time back. But to fit in with the reports I really needed, this particular call up of the report ended up as the best (?) option available. I just ended up having to accept that the #Error was something I would have to put up with.

With current thoughts of someone else needing to use the database, I just hoped I'd be able to tweak that last little niggle.

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[RoyVidar]

hmmm...

Well at least I learnt something new, when using both your and my methods, it works on my machines (a2k/axp).

Should of course have seen it immidiately, in stead of confusing (both you and me;-)) thru this...

I think this might work. Just adding error handling to your ORIGINAL code to trap the error, which I assume is the 2501, cancel of bla bla.

[tt]Private Sub PrintTestDetails_Click()
on error goto PrintTestDetails_Click_Err
Me.Refresh
Dim stDocName As String
stDocName = "ReportOnTestResultsByCustomer_Form"
DoCmd.OpenReport stDocName, acPreview, , "[CustomerNumber]=forms!ActiveCustomers!CustomerNumber"
PrintTestDetails_Click_Exit:
exit sub
PrintTestDetails_Click_Err:
if err.number<>2501 then
msgbox err.description
end if
resume PrintTestDetails_Click_Exit
End Sub[/tt]

Roy-Vidar

 

[ChrisTheAncient]

Back again... After a late supper and glass or two of falling down water.

Now very late UK time so will do deeper work tomorrow after the money-making bit of the day.

I did have an error trap in the report - but not tried it in the form. So, I shall have a go at that when I get back - in about 18 hours time!

I shall report back then

Many thanks for the help so far.

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[ChrisTheAncient]

Roy

Managed to get back to it at last - and as promised, an update.

I put the error routine in and it works fine! Many thanks.

The daft thing is that because I use the code commenter in Office Developer, I would probably have had the error coding in earlier - just as I had in the basic report. But, because I hadn't got that far...

So thanks for sorting me out - and you deserve the star for staying with it and getting me there.

Again, many thanks

Chris


*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[RoyVidar]

Thank you very much, appreciate both your kind words and the star. Specially since I started with a serious misguiging;-)

Roy-Vidar

 

[ChrisTheAncient]

What's a misguiging?

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************

 

[RoyVidar]

Not only misguiding, also mistyping, mis-whatever;-)

 

[ChrisTheAncient]

You've got one of those keyboards like mine! The keys keep moving around when I'm not looking! ;o)

Bed time

G'nite

Chris

*************************************
OK, I'm stoopid. But I'm good at it!
*************************************