Runtime error 2501 Output to / output Query / excel

[ODR via AccessMonster.com]

Hi,

Can some please shed some light on what i am doing wrong. I am outputting a
query to excel with the following code:

Private Sub cmdexcel_Click()
On Error GoTo Handle_Err

DoCmd.OutputTo acOutputQuery, "qry_firststorelst", acFormatXLS, , True

Exit_Err:
Exit Sub

Handle_Err:
Select Case Err.Number
Case 2501
' The OutputTo action was cancelled.
Resume Exit_Err
Case Else
MsgBox Err.Number & " " & Err.Description
Resume Exit_Err
End Select

End Sub

As you can see there is code for trapping the error. Unfortunately this is
not actually happening and i am still getting the runtime error.
I am using access 2003
Thanks

 

[Klatuu]

Error 2501 can be caused by a number of things. If a report is opened but
the recordset returns no records, it will cause a 2501. It can also be
caused by a naming error. That is, there is some field name in the query
that doesn't match up with a field name in the underlying recordset.

It is caused because Access is sending something to Jet it can't understand,
so Jet cancels the operation because it can't complete it.

Be sure your query returns records, and check the names to be sure there are
no errors there.

 

[ODR via AccessMonster.com]

Hi Klatuu,

Sorry i should have explained that i get this error when i cancel the "output
to" in the displayed dailogue box. The query works fine as i have it
returning results in a list box. I then want to be able to output these
results to excel.

Thanks
Sean

Error 2501 can be caused by a number of things. If a report is opened but
the recordset returns no records, it will cause a 2501. It can also be
caused by a naming error. That is, there is some field name in the query
that doesn't match up with a field name in the underlying recordset.

It is caused because Access is sending something to Jet it can't understand,
so Jet cancels the operation because it can't complete it.

Be sure your query returns records, and check the names to be sure there are
no errors there.

Hi,

[quoted text clipped - 25 lines]

I am using access 2003
Thanks

 

[Klatuu]

Then, I guess I don't understand the question.
--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

Sorry i should have explained that i get this error when i cancel the "output
to" in the displayed dailogue box. The query works fine as i have it
returning results in a list box. I then want to be able to output these
results to excel.

Thanks
Sean

Error 2501 can be caused by a number of things. If a report is opened but
the recordset returns no records, it will cause a 2501. It can also be
caused by a naming error. That is, there is some field name in the query
that doesn't match up with a field name in the underlying recordset.

It is caused because Access is sending something to Jet it can't understand,
so Jet cancels the operation because it can't complete it.

Be sure your query returns records, and check the names to be sure there are
no errors there.

Hi,

[quoted text clipped - 25 lines]

I am using access 2003
Thanks

 

[ODR via AccessMonster.com]

Hi Klatuu,

The Query can be outputted to excel fine. The problem i have is that if the
user cancels the operation instead of selecting Ok then i get the following
message box:

Run time error 2501
The OutputTo action was canceled

Then, I guess I don't understand the question.

Hi Klatuu,

[quoted text clipped - 21 lines]

 

[ODR via AccessMonster.com]

Should have said i want to stop this message appearing if Cancel is selected.
The last thing i want is a user clicking on debug and having code displayed!!

Hi Klatuu,

The Query can be outputted to excel fine. The problem i have is that if the
user cancels the operation instead of selecting Ok then i get the following
message box:

Run time error 2501
The OutputTo action was canceled

Then, I guess I don't understand the question.

Hi Klatuu,

[quoted text clipped - 21 lines]

I am using access 2003
Thanks

 

[Klatuu]

Try changing the resumes to Goto:

Handle_Err:
Select Case Err.Number
Case 2501
' The OutputTo action was cancelled.
GoTo Exit_Err
Case Else
MsgBox Err.Number & " " & Err.Description
Goto Exit_Err
End Select

--
Dave Hargis, Microsoft Access MVP

Hi Klatuu,

The Query can be outputted to excel fine. The problem i have is that if the
user cancels the operation instead of selecting Ok then i get the following
message box:

Run time error 2501
The OutputTo action was canceled

Then, I guess I don't understand the question.

Hi Klatuu,

[quoted text clipped - 21 lines]

I am using access 2003
Thanks

 

[ODR via AccessMonster.com]

Hi Klatuu,

This is really strange. I changed to Goto, saved and then tried the same
command and it didn't work. I then closed the database, re-opened it and
tried again and it worked. I then went into the code, (didn't change anything)
closed it and tried again and got the same runtime error. I closed the
database again and then reopened and it works fine. What the heck is going on?


I think its time for me to go home for the day!!

Try changing the resumes to Goto:

Handle_Err:
Select Case Err.Number
Case 2501
' The OutputTo action was cancelled.
GoTo Exit_Err
Case Else
MsgBox Err.Number & " " & Err.Description
Goto Exit_Err
End Select

Hi Klatuu,

[quoted text clipped - 11 lines]

 

[Alex Dybenko]

Hi,
could be some VBA corruption, try to decompile project next time you get it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Hi Klatuu,

This is really strange. I changed to Goto, saved and then tried the same
command and it didn't work. I then closed the database, re-opened it and
tried again and it worked. I then went into the code, (didn't change
anything)
closed it and tried again and got the same runtime error. I closed the
database again and then reopened and it works fine. What the heck is going
on?


I think its time for me to go home for the day!!

Try changing the resumes to Goto:

Handle_Err:
Select Case Err.Number
Case 2501
' The OutputTo action was cancelled.
GoTo Exit_Err
Case Else
MsgBox Err.Number & " " & Err.Description
Goto Exit_Err
End Select

Hi Klatuu,

[quoted text clipped - 11 lines]

I am using access 2003
Thanks