What do i need to change to stop crashing? (1 Viewer)

jimtimber

Registered User.
Local time
Today, 21:49
Joined
Apr 25, 2014
Messages
118
Hi,

The code below is working well, it opens an Excel spreadsheet version of the query I want to run and it saves fine. However, if I click "cancel" instead of choosing to save the spreadsheet, Access crashes out. Any ideas what I need to change please? Thank, Jim (p.s., not sure if you need the command button VBA, or the VBA from the form it opens once clicked?)

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "RegionCode Dialog Box"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acEdit
ReportName = "53_5 Group Wide Training Carer Report Excel"

Exit_Command6_Click:
Exit Sub
Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:49
Joined
Sep 12, 2006
Messages
15,614
which error do you get

cancelling something might be giving you a 2501 error (if it does actually return an error), which is just a standard "object did not open" error, which can be ignored.

ie

Code:
Err_Command6_Click:
'just ignore a 2501 error
 if err<>2501 then
      MsgBox "Error: " & err & " Desc: " & Err.Description
end if
Resume Exit_Command6_Click
 

DavidAtWork

Registered User.
Local time
Today, 21:49
Joined
Oct 25, 2011
Messages
699
There's probably a number of ways you could manage this, but I'd think about your design and ask yourself the question: is there any value in running and opening the spreadsheet with the intention of discarding the spreadsheet, if the answer is No, then you could mange this by getting the user to select a destination folder using Application.FileDialog(msoFileDialogFolderPicker) and a simple Input box to enter the filename (or hard code a filename incorporating today's date), if either of these = "" you can abort the operation.
If the answer is Yes, then you may need to post more of your code to see how it can be managed

David
 

jimtimber

Registered User.
Local time
Today, 21:49
Joined
Apr 25, 2014
Messages
118
Hi Gemma, the error it returns is "Run time error '2501'. The OutputTo Action was cancelled."

Hi David, sometimes the user chooses the wrong option from the combobox on the "RegionCode Dialog Box" Form and then cancels the "output to" box, which causes it to crash. So I would choose the "no" part of your reply. The remaining code for this, which is on the "RegionCode Dialog Box" command button, is pasted below.

Thanks to you both for replying. I'm new to VBA so may need a walkthrough!

Private Sub Command12_Click()
Dim stDocName As String
Dim stLinkCriteria As String

Forms![RegionCode Dialog Box].Visible = False
DoCmd.OpenQuery ReportName$, acNormal, acReadOnly
DoCmd.OutputTo acOutputQuery, , acFormatXLS, , True
DoCmd.Close acQuery, ReportName$, acSaveNo
Forms![RegionCode Dialog Box].Visible = True


End Sub
 

DavidAtWork

Registered User.
Local time
Today, 21:49
Joined
Oct 25, 2011
Messages
699
oh I see you're just using the DoCmd.OutputTo method, you could try using just:
DoCmd.OutputTo acOutputQuery, ReportName$, acFormatXLS, , True

It's not necessary to open the query and have an error trap such as

Code:
Private Sub Command12_Click()
Dim stDocName As String
Dim stLinkCriteria As String
 
On Error GoTo errorEscape
 
Forms![RegionCode Dialog Box].Visible = False
DoCmd.OutputTo acOutputQuery, ReportName$, acFormatXLS, , True
 
Forms![RegionCode Dialog Box].Visible = True
 
Exit Sub
 
errorEscape:
If Err.Number = 2501 Then: Resume Next
 
 
End Sub

David
 

jimtimber

Registered User.
Local time
Today, 21:49
Joined
Apr 25, 2014
Messages
118
Hi David,

That works brilliantly for me, thank you for your time and help. This will also help me with future reports!

Thanks again,

Jim
 

Users who are viewing this thread

Top Bottom