wierd error loops 1

[99mel]

I have the following code in a form with a command button on the form. If I run this code It does what I'm wanting it to do.. i.e loops through the sub 5 times. After the 5th time the if statement is false so i was thinking it should simply msgbox the error and exit the sub. However it loops round the msgbox for a few times then exits the sub?

Can anyone explain whats happening?

Public errortimes As Integer

Private Sub Command1_Click()
On Error GoTo Err
Dim i As Integer

i = "t"

Err:
errortimes = errortimes + 1
If errortimes < 6 Then
Call Command1_Click
End If
MsgBox Err.Description
End Sub

 

[arst06d]

Because you are recursively calling the procedure, the blank msg boxes are displayed by the procedures called earlier.

The error occurred local to the last procedure call, so subsequent ones show no error text.

 

[99mel]

ahh... i see.

Is it possible to exit the sub without having to loop round all the blank errors.

Or is there a better way of going about this?

 

[arst06d]

Not quite sure what you're trying to achieve here - only to display the error message if 6 or more errors occur?

use a static variable in the procedure, increment it each time it's called then check it and display the msg if >=6.


If you just want to loop round 6 times - use a loop in the procedure and don't call it recursively.

 

[99mel]

The code above was just some test code to see what was happening.

I have a program which fires of select/insert and update sql queries to an access db. There are 2 programs doing this so i've found the db is often getting locked by one of the programs.

I'm basically wanting to trap this error and wait for 1/2 a second then try the query(procedure) again. I want to loop round this about 5 times and then if the db is still locked it displays the error message and exits the sub.

Does that make sense?

 

[arst06d]

Can you increase the timeouts on the database itself?

if not then do it in a loop in the one procedure.
If the sql command fails the first time wait and try again.


dim dtTime as datetime

on error resume next

db.execute sqlcommand
do while Err <> 0
err.clear
dtTime = now

'wait 5 secs
do while now< dateadd(&quot;s&quot;,5,dtTime) 'check syntax
doevents
loop

'try again
db.execute sqlcommand

loop

 

[99mel]

great! ill use the on error resume next!

I dont know if its possible to increase the timeout on the db.


cheers!

 

[arst06d]

Looking at access97
Tools>options>advanced tab

have a look at the Number of Update Retries and the Update Retry Interval