By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,121 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Error code - advanced

100+
P: 157
Hello guys!

We have an SQL server which sometimes makes timeouts and connection errors. And we have an function witch writes and updates data in 2 tables on this server. When the SQL server error appears it, in 99%, of the cases, works if we just press the play button in VBA debug. Therefor we have maked an error handling which just tryes again.

However, as this error handling is difficult to test because of maybe 1 or 2 errors a day, we have tested it to not work as we would like it.

Sometimes it dosent try again, sometimes it only try 1 time and then stop code.
Can you see any obious errors in our error handling?

It's a rather long code:
Expand|Select|Wrap|Line Numbers
  1. Function Loksumflytt(Varenr As Long, Antall_tabletter As Long, Fra As String, Til As String, Optional Hendelse As String, Optional Behandlingstype As String, Optional BoksID As String, Optional Batch As String, Optional Conveyor As String, Optional Pasient As String, Optional Kundegruppe As String)
  2. On Error GoTo 0
  3.  
  4. Dim RstLoksum As New ADODB.Recordset
  5. Dim RstHistorikk As New ADODB.Recordset
  6. Dim Aktiv_bruker As String
  7. Dim Slett As String
  8. 'errorCount brukes for å følge med antall "prøv igjen" ved feil
  9. Dim errorCount As Integer
  10. Dim errHandl As New ADODB.Recordset
  11. 'errDel brukes for å vite hvilken del av koden som lagde feilen
  12. Dim errDel As Integer
  13. 'errEgen brukes for å holde en egendefinert feilbeskrivelse
  14. Dim errEgen As String
  15. 'errEgen=" " er muligens unødvendig, ble lagt til for å unngå en "invalid use of null" feil
  16. errEgen = " "
  17. ntlogin = Environ("Username")
  18. Aktiv_bruker = ntlogin
  19.  
  20. If Antall_tabletter < 0 Then
  21.     MsgBox "Antall tabletter må være en positiv verdi" & vbCrLf & vbCrLf & "Lokasjonsflyttingen avbrytes", vbOKOnly, "LogiDose"
  22.     Exit Function
  23. End If
  24.  
  25. With RstLoksum
  26.  
  27. 'Denne koden gjentas flere ganger
  28. 'Hvis det blir error er dette første forsøk i denne delen av koden
  29. errorCount = 0
  30. 'ved error vil koden prøve på nytt fra dette punktet
  31. LokasjonsflyttError1Fortsett:
  32. 'hvis tabellen er åpen..
  33. If .State = adStateOpen Then
  34.     'IKKE lagre endringer
  35.     .CancelUpdate
  36.     'lukk tabellen
  37.     .Close
  38. End If
  39. On Error GoTo LokasjonsflyttError1
  40.  
  41. 'Her starter den vanlige koden
  42. If Not Fra = "Intet" And Not Fra = "Grossist" Then
  43.     .Open "SELECT * from [Lok Loksummer] where varenr=" & Varenr & " AND Lokasjon ='" & Fra & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  44.  
  45.         If .EOF And .BOF Then
  46.             .AddNew
  47.             !Varenr = Varenr
  48.             !Lokasjon = Fra
  49.         End If
  50.  
  51.         If IsNull(![Antall tabletter]) Then
  52.             ![Antall tabletter] = 0
  53.         End If
  54.  
  55.         ![Antall tabletter] = ![Antall tabletter] - Antall_tabletter
  56.  
  57.         If ![Antall tabletter] = 0 Then
  58.             .Delete
  59.             Slett = "ja"
  60.         End If
  61.  
  62.         If Not Slett = "ja" Then
  63.     .Update
  64. On Error GoTo 0
  65.         End If
  66.  
  67.     .Close
  68. End If
  69. Slett = ""
  70.  
  71. 'Hører til error handling, beskrevet tidligere i koden
  72. errorCount = 0
  73. LokasjonsflyttError2Fortsett:
  74. If .State = adStateOpen Then
  75.     .CancelUpdate
  76.     .Close
  77. End If
  78. On Error GoTo LokasjonsflyttError2
  79. 'slutt på det som hører til error handling
  80.  
  81. If Not Til = "Intet" Then
  82.     .Open "SELECT * from [Lok loksummer] where varenr=" & Varenr & " AND lokasjon='" & Til & "'", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  83.         If .EOF And .BOF Then
  84.             .AddNew
  85.             !Varenr = Varenr
  86.             !Lokasjon = Til
  87.         End If
  88.  
  89.         If IsNull(![Antall tabletter]) Then
  90.             ![Antall tabletter] = 0
  91.         End If
  92.  
  93.         ![Antall tabletter] = ![Antall tabletter] + Antall_tabletter
  94.  
  95.         If ![Antall tabletter] = 0 Then
  96.             .Delete
  97.             Slett = "ja"
  98.         End If
  99.  
  100.         If Not Slett = "ja" Then
  101.  
  102.  
  103.         .Update
  104. On Error GoTo 0
  105.         End If
  106.  
  107.     .Close
  108. End If
  109. End With
  110.  
  111. With RstHistorikk
  112.  
  113. 'Hører til error handling, beskrevet tidligere i koden
  114. errorCount = 0
  115. LokasjonsflyttError3Fortsett:
  116. If .State = adStateOpen Then
  117.     .CancelUpdate
  118.     .Close
  119. End If
  120. On Error GoTo LokasjonsflyttError3
  121. 'slutt på det som hører til error handling
  122.  
  123. .Open "SELECT * from [LOK Loksummer - flytthistorikk]", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  124.     .AddNew
  125.     ![Varenr] = Varenr
  126.     !Conveyor = Conveyor
  127.     !Pasient = Pasient
  128.     !Kundegruppe = Kundegruppe
  129.     ![Antall tabletter] = Antall_tabletter
  130.     !Hendelse = Hendelse
  131.     !BoksID = BoksID
  132.     !Batch = Batch
  133.     ![Fra lokasjon] = Fra
  134.     ![Til lokasjon] = Til
  135.     ![flyttet av] = Aktiv_bruker
  136.     ![Flyttet tid] = Now
  137.     ![registrert tid] = Now
  138.     ![Akt] = -1
  139.  
  140.     .Update
  141. On Error GoTo 0
  142. .Close
  143.  
  144. End With
  145. Exit Function
  146.  
  147. 'HER STARTER ERROR HANDLING
  148.  
  149. 'Ved feil i error handling gå direkte til "call feillogg()"
  150. On Error GoTo FeilIErrorHandl
  151.  
  152. 'Lagre hvilken del av koden som genererte feil og gå vidre til LokasjonsflyttErrorLagre
  153. LokasjonsflyttError1:
  154. errDel = 1
  155. MsgBox "Error1"
  156. GoTo LokasjonsflyttErrorLagre
  157. MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError1", vbOKOnly, "LogiDose"
  158. Exit Function
  159.  
  160. LokasjonsflyttError2:
  161. errDel = 2
  162. MsgBox "Error2"
  163. GoTo LokasjonsflyttErrorLagre
  164. MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError2", vbOKOnly, "LogiDose"
  165. Exit Function
  166.  
  167. LokasjonsflyttError3:
  168. errDel = 3
  169. MsgBox "Error3"
  170. GoTo LokasjonsflyttErrorLagre
  171. MsgBox "FEIL I ERROR HANDLING!!" & vbNewLine & vbNewLine & "LokasjonsflyttError3", vbOKOnly, "LogiDose"
  172. Exit Function
  173.  
  174. 'Her begyner "Hoved delen" Lagre hva som ble forsøkt gjort og hva som gikk galt
  175. LokasjonsflyttErrorLagre:
  176. 'dette holder oversikt over hvor mange forsøk som er brukt
  177. errorCount = errorCount + 1
  178.  
  179. 'åpne tabellen [FEIL Loksumflytt]
  180. With errHandl
  181.     .Open "SELECT * FROM [FEIL Loksumflytt] ORDER BY Feil_ID DESC", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
  182.         'Hvis dette er første forsøk: finn en feil id
  183.         If errorCount = 1 Then
  184.             'Hvis det ikke finnes noen oppføringer i [FEIL Loksumflytt] er feil_id=1
  185.             If .BOF And .EOF Then
  186.                 feil_id = 1
  187.             Else
  188.             'tabellen er sortert etter feil_id ny feil_id=gammel feil_id + 1
  189.                 feil_id = !feil_id + 1
  190.             End If
  191.         End If
  192.  
  193.         'Hvis det IKKE er første forsøk starter koden her etter ".open"
  194.         .AddNew
  195.         !feil_id = feil_id
  196.         !forsøk_nummer = errorCount
  197.         !Fra_funksjon = "Loksumflytt"
  198.         !tid = Now
  199.         !bruker = Brukernavn
  200.         !errNum = Err.Number
  201.         !errDes = Err.Description
  202.         !errDel = errDel
  203.         !Varenr = Varenr
  204.         !Antall_tabletter = Antall_tabletter
  205.         !Fra = Fra
  206.         !Til = Til
  207.         'Det under dette punktet er "optional" i funksjonen
  208.         If Not IsNull(Hendelse) Then
  209.             !Hendelse = Hendelse
  210.         End If
  211.         If Not IsNull(Behandlingstype) Then
  212.             !Behandlingstype = Behandlingstype
  213.         End If
  214.         If Not IsNull(BoksID) Then
  215.             !BoksID = BoksID
  216.         End If
  217.         If Not IsNull(Batch) Then
  218.             !Batch = Batch
  219.         End If
  220.         If Not IsNull(Conveyor) Then
  221.             !Conveyor = Conveyor
  222.         End If
  223.         If Not IsNull(Pasient) Then
  224.             !Pasient = Pasient
  225.         End If
  226.         If Not IsNull(Kundegruppe) Then
  227.             !Kundegruppe = Kundegruppe
  228.         End If
  229.         If Not IsNull(errEgen) Then
  230.             !errEgen = errEgen
  231.         End If
  232.         .Update
  233.     .Close
  234. End With
  235.  
  236. 'Hvis det har gått mer enn 9 forsøk
  237. If errorCount > 9 Then
  238. FeilIErrorHandl:
  239.     'FeilLogg lagrer i egen tabell(feillogg) og skriver ut automatisk (til MD_4 per 9/3-2009)
  240.     Call FeilLogg(Varenr, "Lokasjonsflytt", "Antall_tabletter:" & Antall_tabletter & " Fra:" & Fra & _
  241.     " Til:" & Til & " err.number:" & Err.Number & " err.description:" & Err.Description & " tid:" & _
  242.     Now & " Bruker:" & Brukernavn & " ErrEgen:" & errEgen)
  243.  
  244.     'Går til slutten av error handling og fortsetter med neste vare
  245.     'Dette har ikke blitt testet pga problemer med å provosere fram feil
  246.     GoTo ErrorHandlSlutten
  247.     Exit Function
  248. End If
  249.  
  250. 'sSleep(100) = vent 1 sekund
  251. sSleep (100)
  252.  
  253. 'ser hvilken del av koden som feilet
  254. 'Err.clear = tar programmet ut av "feil modus"
  255. 'Prøv igjen (goto "begynelsen av den delen der feilen oppsto")
  256. If errDel = 1 Then
  257.     Err.Clear
  258.     GoTo LokasjonsflyttError1Fortsett
  259. ElseIf errDel = 2 Then
  260.     Err.Clear
  261.     GoTo LokasjonsflyttError2Fortsett
  262. ElseIf errDel = 3 Then
  263.     Err.Clear
  264.     GoTo LokasjonsflyttError3Fortsett
  265. Else
  266.     'Hvis errDel på noen rar måte har blitt noe annet enn det som står her
  267.     'Håndter på samme måte som hvis den bruker mer enn 9 forsøk
  268.     errEgen = "Ukjent errDel: " & errDel
  269.     GoTo FeilIErrorHandl
  270. End If
  271.  
  272. 'Gir opp og fortsetter
  273. 'Skal bare komme hit når FeilLogg har blitt skrevet ut
  274. ErrorHandlSlutten:
  275. Err.Clear
  276. End Function
May 6 '09 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,308
It would be helpful if you would tell us :
  1. Where the code errors in the first place.
  2. Which error routine is being invoked.
May 6 '09 #2

ADezii
Expert 5K+
P: 8,616
As indicated by NeoPa, you are going to have to pinpoint the context in which the Error is happening. The large number of Unconditional GoTo Statements (Line Numbers: 156, 163, 170, etc.) makes this type of coding very difficult to Debug. This is one reason why the use of Unconditional GoTos is frowned upon (outside of On Error GoTo).
May 6 '09 #3

100+
P: 157
Ok..

Errors occour, sometimes, on following lines

43.
63.
82.
103.
123.
140.

I really have very little experience in error coding cause i usually make the code avoid error instead of handling them, but this is the SQL server which makes my nightsleep hard :P

When an error happends, i want the code to basically try again. E.G. is when error occours in line 82. i want it to make a log entry and try again at line 82. As you can see we have different pointers to error handling and which uses the Goto command.

When error on 43 or 63 is uses the "On Error GoTo LokasjonsflyttError1 "
LokasjonsflyttError1 sets the "ErrDel = 1" and "Goto LokasjonsflyttErrorLagre "

LokasjonsflyttErrorLagre saves the error message and uses the "ErrDel" to go back to line 43 trough this expression:
Expand|Select|Wrap|Line Numbers
  1. If errDel = 1 Then 
  2.     Err.Clear 
  3.     GoTo LokasjonsflyttError1Fortsett
  4. end if
Here is one problem, when it makes one of this loops it only makes 1 and if error again the code stops. And i dont know why. I would like it to try up to 9 times before it quits the code.
May 7 '09 #4

FishVal
Expert 2.5K+
P: 2,653
@MrDeej
Try to replace it with

Expand|Select|Wrap|Line Numbers
  1. If errDel=1 Then Resume LokasjonsflyttError1Fortsett
  2.  
May 7 '09 #5

100+
P: 157
Ok.. I will implement this and report back here when an error occour
May 7 '09 #6

ADezii
Expert 5K+
P: 8,616
MrDeej, I do not mean to sound harsh, but as long as you employ this current logic as far as Unconditional Branching to handle Errors, this will just be the beginning of the end as far as problems and tracing them go. That being said, you clear the Error and branch to LokasjonsflyttError1Fortsett:. Should another, or the same Error, occur after Line #78, you now branch to LokasjonsflyttError2: where errDel now equals 2 and you again return to LokasjonsflyttErrorLagre but errDel now equals 2.
May 7 '09 #7

NeoPa
Expert Mod 15k+
P: 31,308
If, in an error routine, you use Go To to branch back to the code you want to retry (as opposed to using Resume ...), then the compiler will never know that you're trying again, and can only assume you are still handling the first error.

When your code errors within an error handler it will not try to re-enter the error handler (It already knows that code fails), but it will go to the next level up and allow that code to handle the new error. Essentially unwrapping itself like an onion.

I strongly recommend you read up on this subject in the help system. It's a good idea generally to check out a subject in help or the manual before trying to use it. Things work so much more easily that way.
May 7 '09 #8

100+
P: 157
I have looked in some help files for Error handling. But i cant find an error handling which just tryes again.

Basic to this subject.

If i have no error code, the debug dialog pops up and let me debug the code on error. Here i just have to press play and it now works. This is because the errors are not in the code, but in the connection to the SQL server.

Therefore we have made this code, which i could not find any info on, to make the code just try again.

Because that is what i basically want: "On Error Try Again" 9 times before stopping

ADezii: I didnt understand your post fully, what do you mean by branching?
May 8 '09 #9

NeoPa
Expert Mod 15k+
P: 31,308
  1. From Access hit Alt-F11 to go to the IDE window.
  2. From there hit Ctrl-G to open the Debug Pane.
  3. From there type in Error and hit F1 (Context sensitive help).
  4. When prompted select On Error Statement.
  5. Read this all through. Some of the notes lower down are quite important for understanding.
  6. Click on See Also, and select Resume Statement.
  7. Read through this smaller section.
  8. Select any other items you see that you want to understand in more detail.
You should now have a thorough understanding of the basic issues and will grasp :
  1. Why your code didn't work.
  2. What you need to do to ensure it always does in future.
This is well worth putting in the small amout of effort to understand.

Good luck with your project.
May 8 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.