UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dealing With Error 0, Any Versions    
 
   
BentBrain
post Feb 29 2020, 12:20 AM
Post#1



Posts: 563
Joined: 10-February 03
From: Thailand


Hi All
My question is around MS Access error handle for SQL Server.

See attached image.

I understand that when the user closes the form that has a required field with no entry.

SQL server let MS access know but giving the error and MS Access then hangs.

QUESTION.
Is there a way to handle this error so MS Access does not Hang?

Thanks in Advance
BentBrain
Attached File(s)
Attached File  MS_Access_SQL_Server_error.JPG ( 19.22K )Number of downloads: 0
 

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
June7
post Feb 29 2020, 01:39 AM
Post#2



Posts: 1,394
Joined: 25-January 16
From: The Great Land


Can error handler code deal with this: http://allenbrowne.com/ser-23a.html

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
BentBrain
post Feb 29 2020, 01:47 AM
Post#3



Posts: 563
Joined: 10-February 03
From: Thailand


Thanks June7
But I am already doing that.

As the image file is the message box from my error handler.
i.e.
ErrorinHandler:
'if error occors in handler then show message
Close #intErrorFileHandle

MsgBox "Error in Handler " & Err.Number & " " & Err.Description & vbCrLf & _
" intErrorFileHandle " & intErrorFileHandle & ErrSrc & vbCrLf & " Active frm - " & ScrnActvFrmNm & " Control - " & ctrlfnctnm
Resume Next 'try resume instead of exit for now ExitinHandler
...

My Main issue is MS Access hangs after this error and requires a manual shutdown.

Regards
BB

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
June7
post Feb 29 2020, 02:36 AM
Post#4



Posts: 1,394
Joined: 25-January 16
From: The Great Land


I probably can't help. I don't use SQLServer.

I did a little research and found one reference indicating a stored procedure could be causing issue but there could be any number of causes.

Suggest you disable error handler and step debug.


--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
cheekybuddha
post Feb 29 2020, 04:13 AM
Post#5


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


Can you show your whole code.

Err = 0 means no error.


--------------------


Regards,

David Marten
Go to the top of the page
 
BentBrain
post Feb 29 2020, 04:26 AM
Post#6



Posts: 563
Joined: 10-February 03
From: Thailand


Hi Buddha,

Take a look - FYI error 0 happens when user leaves a form with required fields on a liked SQL table.

*********************************************************
Public Sub gsubErrorHandler(ErrNum As Integer, ErrDesc As String, ErrSrc As String, ScrnActvFrmNm As String, ctrlfnctnm As String, errMsgStr As String)
On Error GoTo ErrorinHandler
Dim intErrorFileHandle As Long
Dim errdate As String
' Turn off the Hourglass & reset Warnings
DoCmd.Hourglass False
DoCmd.SetWarnings True

errdate = Format$(Date, "dd-mmm-yy")
intErrorFileHandle = FreeFile

errFileName = CurrentProject.Path & "\Errors\"


Open errFileName For Append As #intErrorFileHandle
Select Case ErrNum
Case 0 ' dont know what this is. need to research error
Exit Sub

Case 6 'OverFlow error Don't know why.

Case 2501 ' The Close action was canceled.
Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
MsgBox "Closing Error " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " The Close action was canceled"

Case 3218 ' Could not update; currently locked.
Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
MsgBox "Could not update, currently locked. " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " Currently locked. Please try Again "

Case 2424 ' cannot find field, control, or property .
Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
MsgBox "Database cannot find field, control, or property " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " Field, Control, or Property cannot be found "

Case 3167 'Record deleted error happens with mutliquery pages

Case 3021 'Cancel Print Known issues after install office sp3
'Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf

Case 3343
Print #intErrorFileHandle, Date$, Time$, ErrSrc, "DB Crash ", ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & " " & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf
MsgBox "Please contact IT SUPPORT" & vbCrLf & "and convey the following information:" & vbCrLf & vbCrLf & _
" Database requires Repair " & vbCrLf & "Please log out immediately of DB" & ErrDesc, vbOKOnly + vbExclamation, "Application Error - The database will be restored shortly."
DoCmd.RunCommand acCmdSaveRecord
Case Else
Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
MsgBox "Application Error " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " Application Error"
End Select
Close #intErrorFileHandle

ErrorFlag = True

Exit Sub
ExitinHandler:
Exit Sub

ErrorinHandler:
'if error occors in handler then show message
Close #intErrorFileHandle

errFileName = CurrentProject.Path & "\Errors\HandlerErrors.txt"

Open errFileName For Append As #1
Print #1, "Handler Error :", ScrnActvFrmNm, Date$, Time$, Err.Number, Err.Description, Err.Source & " " & GetTerminal & vbCrLf
Close #1
MsgBox "Error in Handler " & Err.Number & " " & Err.Description & vbCrLf & _
" intErrorFileHandle " & intErrorFileHandle & ErrSrc & vbCrLf & " Active frm - " & ScrnActvFrmNm & " Control - " & ctrlfnctnm
Resume Next 'try resume instead of exit for now ExitinHandler

End Sub
This post has been edited by BentBrain: Feb 29 2020, 04:26 AM

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 29 2020, 04:33 AM
Post#7


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


I'm re-posting your code using code tags to make it wasier to see what's going on:
CODE
Public Sub gsubErrorHandler(ErrNum As Integer, ErrDesc As String, ErrSrc As String, ScrnActvFrmNm As String, ctrlfnctnm As String, errMsgStr As String)
On Error GoTo ErrorinHandler

  Dim intErrorFileHandle As Long
  Dim errdate As String
  ' Turn off the Hourglass & reset Warnings
  DoCmd.Hourglass False
  DoCmd.SetWarnings True

  errdate = Format$(Date, "dd-mmm-yy")
  intErrorFileHandle = FreeFile

  errFileName = CurrentProject.Path & "\Errors\"

  Open errFileName For Append As #intErrorFileHandle
  Select Case ErrNum
  Case 0 ' dont know what this is. need to research error
    Exit Sub

  Case 6 'OverFlow error Don't know why.

  Case 2501 ' The Close action was canceled.
    Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
    MsgBox "Closing Error " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " The Close action was canceled"

  Case 3218 ' Could not update; currently locked.
    Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
    MsgBox "Could not update, currently locked. " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " Currently locked. Please try Again "

  Case 2424 ' cannot find field, control, or property .
    Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
    MsgBox "Database cannot find field, control, or property " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " Field, Control, or Property cannot be found "

  Case 3167 'Record deleted error happens with mutliquery pages

  Case 3021 'Cancel Print Known issues after install office sp3
  'Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf

  Case 3343
    Print #intErrorFileHandle, Date$, Time$, ErrSrc, "DB Crash ", ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & " " & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf
    MsgBox "Please contact IT SUPPORT" & vbCrLf & "and convey the following information:" & vbCrLf & vbCrLf & _
           " Database requires Repair " & vbCrLf & "Please log out immediately of DB" & ErrDesc, vbOKOnly + vbExclamation, "Application Error - The database will be restored shortly."
    DoCmd.RunCommand acCmdSaveRecord
  Case Else
    Print #intErrorFileHandle, Date$, Time$, ErrSrc, ScrnActvFrmNm, ctrlfnctnm, ErrDesc, ErrNum, "Terminal ID :" & GetTerminal & GetUserNames & vbCrLf & AllOpenObjects & vbCrLf & vbCrLf
    MsgBox "Application Error " & vbCrLf & ErrDesc & ErrNum, vbOKOnly + vbExclamation, " Application Error"
  End Select
  Close #intErrorFileHandle

  ErrorFlag = True

  Exit Sub
ExitinHandler:
  Exit Sub

ErrorinHandler:
  'if error occors in handler then show message
  Close #intErrorFileHandle

  errFileName = CurrentProject.Path & "\Errors\HandlerErrors.txt"

  Open errFileName For Append As #1
  Print #1, "Handler Error :", ScrnActvFrmNm, Date$, Time$, Err.Number, Err.Description, Err.Source & " " & GetTerminal & vbCrLf
  Close #1
  MsgBox "Error in Handler " & Err.Number & " " & Err.Description & vbCrLf & _
  " intErrorFileHandle " & intErrorFileHandle & ErrSrc & vbCrLf & " Active frm - " & ScrnActvFrmNm & " Control - " & ctrlfnctnm
  Resume Next 'try resume instead of exit for now ExitinHandler

End Sub


These lines look suspect:
CODE
' ...
  errFileName = CurrentProject.Path & "\Errors\"

  Open errFileName For Append As #intErrorFileHandle
' ...

Don't you need a file name?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 29 2020, 04:40 AM
Post#8


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


Also, you have at the end of your error handler:
CODE
' ...
  Resume Next 'try resume instead of exit for now ExitinHandler
' ...


You will go through the handler again with your 0 error if the error occurred within the handler itself.

This line:
CODE
ErrorinHandler:
  'if error occors in handler then show message
  Close #intErrorFileHandle
' ...

could perhaps cause an error if the file wasn't opened properly due to improper filename above.

--------------------


Regards,

David Marten
Go to the top of the page
 
BentBrain
post Feb 29 2020, 04:48 AM
Post#9



Posts: 563
Joined: 10-February 03
From: Thailand


No. That's not it. as the errors are outputting to HandlerErrors.txt

System locks up after this code - How to prevent MS Access from locking up and having to restart?
********************
ErrorinHandler:
'if error occurs in handler then show message
Close #intErrorFileHandle

errFileName = CurrentProject.Path & "\Errors\HandlerErrors.txt"

Open errFileName For Append As #1
Print #1, "Handler Error :", ScrnActvFrmNm, Date$, Time$, Err.Number, Err.Description, Err.Source & " " & GetTerminal & vbCrLf
Close #1
MsgBox "Error in Handler " & Err.Number & " " & Err.Description & vbCrLf & _
" intErrorFileHandle " & intErrorFileHandle & ErrSrc & vbCrLf & " Active frm - " & ScrnActvFrmNm & " Control - " & ctrlfnctnm
Resume Next 'try resume instead of exit for now ExitinHandler
end sub

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 29 2020, 04:57 AM
Post#10


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


How can you open a file with no name? Does that not cause an error leading you in to the handler ErrorinHandler:?

--------------------


Regards,

David Marten
Go to the top of the page
 
BentBrain
post Feb 29 2020, 05:12 AM
Post#11



Posts: 563
Joined: 10-February 03
From: Thailand


Hi Buddha
Great points.

Looks like that is what was happening,

"You will go through the handler again with your 0 error if the error occurred within the handler itself."

Changed the code and now it does not freeze.

Thank you again.

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 29 2020, 05:29 AM
Post#12


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


Just to make sure, you saw the potential issue I pointed out eat the end of my Post#7 ?

--------------------


Regards,

David Marten
Go to the top of the page
 
BentBrain
post Feb 29 2020, 06:02 AM
Post#13



Posts: 563
Joined: 10-February 03
From: Thailand


Hi Buddha,
Yes noted and also updated.

Thank you

--------------------
"There is no excellent beauty that hath not some strangeness in the proportion" Francis Bacon
Go to the top of the page
 
cheekybuddha
post Feb 29 2020, 06:15 AM
Post#14


UtterAccess Moderator
Posts: 12,804
Joined: 6-December 03
From: Telegraph Hill


yw.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th May 2020 - 08:22 PM