Error (1 Viewer)

[ECEstudent]

Hi, my code works just fine but sometimes there's a run time error (i think that was it) where the user would just need to press 'F5' to keep it running. It does this bc I am using access and sql and my tables are kinda in both and there's no way I can change that. Is there a way of avoiding pressing F5? Can the code just do it on its on and keep going? Not sure if that's possible but it would be SO awesome if it could. Please let me know! Thanks! -Laura

 

[CJ_London]

you need to trap errors and handle them correctly as part of good db design.

Errors can be trapped using

On Error GoTo errCtrl

Where ErrCtrl is part of your code


here are some links that will help decide what is best for you

http://allenbrowne.com/ser-23a.html
http://msdn.microsoft.com/en-us/lib...ndVBA_IntroductionToDebuggingAndErrorHandling
http://office.microsoft.com/en-gb/access-help/onerror-macro-action-HA001226457.aspx

You can also use

On Error Resume Next

but this should only be used with caution where you know an error could occur but can be safely ignored

 

[ECEstudent]

um....i still don't know what to do with the whole F5 thing. There's really no 'Error' in my code. just because my code takes a while to link between Access and SQL, I would just need to press the button 'F5' on my keyboard...is there a way of getting the same result of pressing that button in code on 'Error'? Thanks.

 

[jdraw]

Further to CJ's suggestions, you may get some ideas from the info at this link. It may not apply, but it is a good reference from a well respected source.

The -there is sometimes a run time error- is the key phrase.

see
http://blog.fmsinc.com/microsoft-access-inconsistent-compile-error-for-a-field-reference-in-a-form/

 

[pr2-eugin]

That is what CJ suggests ECEstudent.. What you need in is in the part of the code, where it happens you can use the Error Handler to "simulate" the F5 press... Combination of SendKeys and ErrorHandler is what you need..

I might as well add another great reference to Error Handling in VBA, by Chip Pearson..

 

[TJPoorman]

I'll throw my two cents in here too.
I would avoid using SendKeys, but you could always use an error trapping loop. Though I would use some sort of counter to prevent an endless loop. Something like:

Dim intCounter as Integer
 
On Error GoTo errHandler
 
'Code here
Exit Function
 
errHandler:
    If err.Number = 1234 Then    'Change to error code
        If intCounter < 5 Then
            Resume
            intCounter = intCounter + 1
        Else
            msgbox "Error, connection timed out"
        End If
    Else
        msgbox "Error blah blah blah"
    End If

 

[ECEstudent]

Ok. So far I understand what to do when there's a specific type of error...

[FONT=Courier New][COLOR=#003366]    On Error Goto ErrHandler:
    N = 1 / 0    ' cause an error
    '
    ' more code
    '
    Exit Sub 
    ErrHandler:
    ' error handling code
    Resume Next
    End Sub [/COLOR][/FONT]

But I still don't understand the whole F5 thing. How do you control a button on a computer keyboard through code?

 

[ECEstudent]

Also, where would I include the ' On Error Goto ErrHandler ' ? The whole need of pressing button 'F5' can happen anywhere where my code is referring to a SQL linked table. Would I just include it everywhere there's a reference?

 

[pr2-eugin]

But I still don't understand the whole F5 thing. How do you control a button on a computer keyboard through code?

That's where SendKeys come in play..

TJPoorman, I understand what you are getting at, but when Resuming the code I think it will very soon hit the counter value 5.. This is purely by theory as the compilation in general is very fast.. As the OP had mentioned, they have to Press F5, the reason I suggested SendKeys.

Also, where would I include the ' On Error Goto ErrHandler ' ? The whole need of pressing button 'F5' can happen anywhere where my code is referring to a SQL linked table. Would I just include it everywhere there's a reference?

Have it at the top of the Code where it starts.. As TJ has shown in the pseudo code..

 

[TJPoorman]

The only reason I suggested the Resume is from what I can gather of the OP's original problem is there is a connection time-out. If this is the case, in theory, each time the Resume command hit the erroring line of code it would go through the same attempting to connect sequence.

ECEstudent, you may want to look at your connection time-out settings as this may be where the problem lies.

 

[ECEstudent]

Oh ok. Like this then:

.SendKeys({F5}, Wait)

Right?

 

[ECEstudent]

Dim intCounter as Integer

On Error GoTo errHandler

'Code here
Exit Function

errHandler:
If err.Number = 1234 Then 'Change to error code

.SendKeys({F5}, Wait)

Else
msgbox "Error whatever"
End If

 

[ECEstudent]

TJPoorman it was a connection time out! Would my suggested code still work though or would I just say 'Resume'?

 

[pr2-eugin]

No.. Like this..

SendKeys([COLOR=Red][B]"[/B][/COLOR]{F5}[COLOR=Red][B]"[/B][B], True[/B][/COLOR])

 

[pr2-eugin]

Try TJ's code.. I need to know what the outcome would be.. I know it does not happen all the time, but use the code and see if it works, without SendKeys..

 

[ECEstudent]

Sure. The connection time out happens randomly throught the day since it depends on how busy the company server is. So......I'm not completely sure how I would test that but I'll try. Thanks you guys. I'll give it a shot and let you know!

 

[ECEstudent]

Ok. I've been trying to test this out for a while now and my DB is behaving awfully well lately! Which is great! Except I'm worried it won't be like this once I submit my code in to my supervisor...Does anyone know how to TEMPORARILY change the connection timeout value? I found this source ( http://support.microsoft.com/kb/153756 ) but I'm kinda hesitatant to use it just because I'm worried it's not going to be like that temporarily. Thanks!

 

[ECEstudent]

The reason I'm doing this is because I want to get the error code from the error message so I can use it in my code to tell my program what to do in that case.

 

[TJPoorman]

Here's another link on how to change your timeout:

http://windowssecrets.com/forums/sh...DBC-Query-Timing-Out-Access-2007-(Office-2007)