runtime error 2501 help 1

[sdimaggio]

I have a button on a print dialog form which previews or prints reports. The code on the "printbutton" is OnClick event.

Private Sub printbutton_Click()
Dim ReportName, DateFieldName
Select Case Me.PickReport
Case 1
ReportName = "rpt_deliveries"
DateFieldName = "DeliveryDate"
Case 2
DateFieldName = "FiscalPeriod"
ReportName = "Basic Product List"
End Select
DoCmd.OpenReport _
ReportName:=ReportName, _
view:=Me.OutputMode, _
Wherecondition:=DateFieldName & " Between #" & _
Me.StartDate & "# AND #" & _
Me.EndDate & "#"
End Sub

In the report named rpt_deliveries in the OnNoData event I have the following code:

MsgBox "There is no data for this report"
Cancel = True

Everything works fine when there is no data. (i.e. the msgbox pops up and I hit OK button) However, I then get an error with the following code from the print dialog form highlighted in yellow:

DoCmd.OpenReport _
ReportName:=ReportName, _
view:=Me.OutputMode, _
Wherecondition:=DateFieldName & " Between #" & _
Me.StartDate & "# AND #" & _
Me.EndDate & "#"

I need something that will close down the form or better yet, ignor the error since there is no OnNoData event on the print dialog box.

Any Suggestions?

 

[KenReay]

hi

you have answewred your own question, you need on error goto ... code in the onlclick event of the button, then trap ang ignore error 2501 Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[sdimaggio]

I'm having trouble here. Could you show me.

thanks

 

[KenReay]

Hi

Private Sub printbutton_Click()
On Error GoTo Error_PrintButton
Dim ReportName, DateFieldName
Select Case Me.PickReport
Case 1
ReportName = "rpt_deliveries"
DateFieldName = "DeliveryDate"
Case 2
DateFieldName = "FiscalPeriod"
ReportName = "Basic Product List"
End Select
DoCmd.OpenReport _
ReportName:=ReportName, _
view:=Me.OutputMode, _
Wherecondition:=DateFieldName & " Between #" & _
Me.StartDate & "# AND #" & _
Me.EndDate & "#"
Exit_PrintButton:
Exit Sub
Error_PrintButton:
If Err.Number = 2501 Then
resume exit_PrintButton
Else
msgBox "Error " & err.number & " " & err.description
end If
End Sub

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[sdimaggio]

Thanks a million!

Steve

 

[marcus101]

Guys:

I've done pretty much the same thing as you have, only I've opened my reports via a form using:

DoCmd.OpenReport (name), acPrintPreview

to open it...

I use the 2501 error trap pretty much EXACTLY as you have it but Access 2000 always hangs on it. I'm using Access 2000 with SP1.

I get as far as seeing the onNoData MsgBox, and that works, but when I click OK, it disappears and doesn't return control of the form or Access 2000 to me.

It just sits there. I've tried other things, like forcefully trying to close the form, but that gives me Error 13: type mismatch errors.

Help(?)

marcus

 

[KenReay]

Hi

You say:

"I use the 2501 error trap pretty much EXACTLY as you have it but Access 2000 always hangs on it. I'm using Access 2000 with SP1."

Does "pretty much EXACTLY" mean EXACTLY of not?

Why not post your code and give us a fighting chance?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[marcus101]

Ken:

I have code that changes the necessary SQL string based on what is put in the form. That part works fine, and is too lengthy to post.

My problems didn't crop up until I tried to add the onNoData event code to the report. I would get a message box, but then the application would return error 2501.

I then added error trap code to better diagnose, but still no luck.

I have to open up the report and change the source so I can make sure I run the specifics for the report I need - this part is the only area that differs from yours - and may be the reason why there is a lockup.

Fragment #1 - Form button OnClick fragment...

*** begin form button vba fragment ***

On Error GoTo HandleErr

(vba code with a form value to act as a form selector string, actVal)

(more code for selecting and setting an SQL string, str1)

DoCmd.Echo False
DoCmd.OpenReport actVal, acViewDesign

With Reports(actVal)
.RecordSource = str1
DoCmd.Close , , acSaveYes

DoCmd.OpenReport actVal, acViewPreview
DoCmd.Echo True

End With

ExitHere:

Exit Sub

HandleErr:

Select Case Err.Number

Case 2501

Resume ExitHere

' do nothing if default 2501 error emerges.

Case Else

' print a description of the error if not 2501

MsgBox Err.Number & ": " & Err.Description

End Select

End Sub

*** end form button vba fragment ***

Fragment #2 - Report Fragment - onNoDataEvent.

** begin report event vba fragment **

Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records found for these criteria. Press OK to continue.", _
vbExclamation, "No Records to Print"

Cancel = True

End Sub

** end report event vba fragment **

Fragment #2 works perfectly, except for the part where it is actually showing the report. I see the message box, click OK, and then it just hangs up, report, form, the entire application.

Likewise, most of Fragment #1 also works fine, except for the point where I get to the error trap. If there are different errors, they print okay.

As I said, it's 'almost' identical - maybe it's my use of DoCmd.Echo in changing the record source that is throwing things off somewhere?

It's the only part of the code that is NOT the same as the examples that you are using.

Thanks for your help.

marcus

 

[KenReay]

Hi

That is a novel way to do it, never tried that before.

What I would normally do is either:

build the SQL string in the onclick event of the form, then set a form control (say txtSQL) to the SQL string

in the onopen event of the report I would set .Recordsource = Forms!MyForm!txtSQL and .Requery

OR

I would build the SQL string in the onopen event of the report (addressing the form variables via Forms!MyForm!MyVar type syntax), .Recordsource = strSQL

Not saying your method does not work, just saying I have never tried that.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[marcus101]

Ken:

Have yet to do more extensive testing, but commenting out DoCmd.Echo fixes the lockup problem, so yeah, I guess it works.

NOW. (grin)

I wanted Echo in place so it would minimise the possibility (however small) of the user clicking on the change to the record source as it happens when the form/report code is running, potentially damaging the process of altering the source for the report.

IE: I don't want the user to physically "SEE" the change in the record source occur in case they click on something by accident and screw the source string up.

But the machines I am using are fast enough that it only pops up for a second or so anyway so it is not a big deal in the long run.

Your suggestion of splitting it up into different report actions kind of solves this problem as well due to the difference in approach - kind of minimises the need for using the Echo command in there depending on how you employ it - food for thought for me, for sure.

Just for curiosity's sake, I will try to reinstate DoCmd.Echo as I have it, but will move the Echo True command outside of the With block.

That may be why I get a hold up - the with block can't end right and therefore can't show the report nor fulfill the logic properly.

At some point in the near future, I'll rewrite the code so it uses your approach and let you know how it goes. I've seen a lot of mentions of .Requery in past posts for different uses but have yet to use it myself - here is a good opportunity.

I'm still pretty new to using multiple actions, but your suggestion to saving a string to a form control for future actions via a report is a good one - should have thought of it - it's nice and reusable.

Thanks again.

marcus101

 

[vitaminD12]

I had similar problem recently, this helped:
ref: building/previewing/printing reports in Access 2003

Apparently, report related functinality (building/previewing/printing) in MS Access DBMS is directly effected by the way printing is set up on users PC. Printer that is set up as a default and its driver(s) may cause Access reporting to break!

It has been noticed that combination of MS Windows XP Professional OS, Office 2003 (Access 2003), and HP LaserJet 5/5M printer was causinf break down of reporting. In Windows 2000 Professional environment with the same Office and Printer versions/models/setups reporting was stable and functining properly.

While in the process of installing new driver(s) for HP LaserJet 5/5M printer trivial manipulation with default printers was suggested and "fixed" user with report accessing.
HP LaserJet 4100 PCL 6 network printer was installed on user box and set up as adefault.
After previewing the report in Access user was able to Print the report to the original printer
(in pPrint dialog select another - not default - printer).

We expect the problem to be fixed permanntly after installing appropriate/new driver(s) for
HP LaserJet 5/5M printer on user PC.