Runtime error

[fishhead]

HI I'm getting a runtime error that i've traced back to this procedure.


I'm not certain what is wrong with this and was hoping someone would see thing I don't?




Option Compare Database




Private Sub Form_Timer()


If Time >= #9:28:00 AM# And Time <= #4:32:00 PM# Then
CurrentDb.Execute "qryMasterMinuteTrackingHISTORYupdates"
End If


End Sub

The error message i'm getting is as follows:

EXECUTION OF THIS APPLICATION AS STOPPED DUE TO A RUN-TIME ERROR.
The application can't continue and will shut down.

thanks in advance

[Micron]

That's pretty vague. The line that causes the error is usually helpful as well as the error text and number. You've provided the text but not a number, so that makes me think this is a custom error message, but also because it's not a message I'm familiar with. Then again, there are 2 or 3 thousand error messages.
I'm going to guess that the issue is with the query call. Does that query run if you open it from the nav pane?

Advise that you use Option Explicit as well as Option Compare

[fishhead]

That's pretty vague. The line that causes the error is usually helpful as well as the error text and number. You've provided the text but not a number, so that makes me think this is a custom error message, but also because it's not a message I'm familiar with. Then again, there are 2 or 3 thousand error messages.
I'm going to guess that the issue is with the query call. Does that query run if you open it from the nav pane?

Advise that you use Option Explicit as well as Option Compare

I agree that's pretty vague but that's all access is giving me.
I've checked the underlying query and all seems good.
This form is based on an APPEND query and when i manually run the query it works perfectly.

When I run this query MSACCESS give me the message "you are about tu run an append query that will modify data in your table.....) when the user clicks YES you get another message saying "you are about to append 41 rows"
do you think these messages are causing the problem with runtime?

Any other suggestions perhaps?

thanks,

[Micron]

No, those are typical warnings but IMHO they shouldn't be showing up if you're using the Execute method of the CurrentDb object. I always add the ,dbFailOnError parameter, otherwise if the query fails to run, you aren't supposed to get any kind of message. My guess is that there is some event or method on the form - such as Current or Requery - that is causing the issue. If the query runs independently as you say, then I'd say the problem lies elsewhere which can make this hard to uncover without being able to look into it. So my suggestion would be to copy the db then compact/repair the copy and zip it and post it here with instructions on how to replicate the error.

[davegri]

Are you experiencing the error in the Access Runtime environment? That's the only time I've seen that particular error. It will display and kick you out if ANY unhandled error occurs.
How are you setting the TimerInterval and what is it set to? It can be set in the form's property sheet or in the form load event. A setting of 10000 would trigger every 10 seconds. With that setting, the query would run dozens of times if the form is loaded during the stipulated times specified by the IF statement. Is that desirable? If set too low, it could cause the updates to stack up and eventually error out.

[Shadow9449]

1. Instead of running it repeatedly in a timer, would you consider just running it once to see what happens?

2. Do you have any sort of error handling? if you are using the Runtime, you MUST have error handling or please expect Runtime Errors. A simple routine like this would be helpful:

On Error GoTo Err_Handle

...

...

...
lots of code here


Exit_err_handle:
    Exit Sub


Err_Handle:
    MsgBox Err.Description, vbExclamation, "Error Encountered"
    Resume Exit_err_handle

That way it will help pinpoint where things are going wrong so you can resolve it.

[fishhead]

Are you experiencing the error in the Access Runtime environment? That's the only time I've seen that particular error. It will display and kick you out if ANY unhandled error occurs.
How are you setting the TimerInterval and what is it set to? It can be set in the form's property sheet or in the form load event. A setting of 10000 would trigger every 10 seconds. With that setting, the query would run dozens of times if the form is loaded during the stipulated times specified by the IF statement. Is that desirable? If set too low, it could cause the updates to stack up and eventually error out.

thanks Davegri,
yes this error happens in the Access Runtime Environment (my understanding being on a computer that doesn't have msaccess but rather runtime).
yes it kicks me out and shuts down the program.

The timeinterval is set at 80000 (every 80 seconds) and the event procedure is as follows:

If Time >= #9:28:00 AM# And Time <= #4:32:00 PM# Then
CurrentDb.Execute "qryMasterMinuteTrackingHISTORYupdates"
End If

Note the query is an update query.

Can you suggest a better way to handle this function?

thanks

[fishhead]

thanks Shadow,
unfortunately i need to run this every 60-80 seconds.

I'm not sure what error handling means. I have a little coding experience but not a lot.
what do i do with your code above exactly?

[Shadow9449]

thanks Shadow,
unfortunately i need to run this every 60-80 seconds.

I'm not sure what error handling means. I have a little coding experience but not a lot.
what do i do with your code above exactly?

When you use the Access Runtime, if anything goes wrong, it only has one way to deal with it: issue a runtime error and shut down. That kind of sucks if you're trying to develop and pinpoint the cause of errors, but sucks even more when your users or clients face the program turning off that way with no explanation.

Fortunately, you can tell your VBA code that if something goes wrong, instead of shutting down, to handle it in a way that will help the developer or be meaningful to the user.

The code that I showed you starts with:

On Error GoTo Err_Handle

That just means: "if you encounter any problems, then skip down to the section of this block of code labelled "Err_Handle" and do what it says there instead of crapping out"

Then comes your code.

Then comes that label I mentioned that looks like this:

Exit_err_handle:
    Exit Sub




Err_Handle:
    MsgBox Err.Description, vbExclamation, "Error Encountered"
    Resume Exit_err_handle

The Err_Handle: is the label that we skip to when we encounter a problem.
The MsgBox pops up a message with a description of what went wrong.
The Resume line tells it where we want to go next. That's just a line of code that says to exit the sub rather than crapping out.

So when you put it all together with the first post, it would look like this:

Private Sub Form_Timer()

On Error GoTo Err_Handle

If Time >= #9:28:00 AM# And Time <= #4:32:00 PM# Then
CurrentDb.Execute "qryMasterMinuteTrackingHISTORYupdates"
End If

Exit_err_handle:
    Exit Sub

Err_Handle:
    MsgBox Err.Description, vbExclamation, "Error Encountered"
    Resume Exit_err_handle

I hope this makes sense.

Protip: if you get into VBA coding any deeper, especially if you plan to deploy using the runtime, then get into the habit of using this kind of error trapping and you'll be very thankful! Take a few minutes and Google something like "VBA Error handling" and see if you can find some good articles that explain it more comprehensive than I did!

Good luck

[fishhead]

Thanks for the step by step explanation Shadow, very helpful.
Below is the code i revised (i believe as per your instruction)
I'm still getting the same error message as before so it doesn't appear to be doing anything?

Is there something I'm missing perhaps?


Option Compare Database






Private Sub Form_Timer()


On Error GoTo Err_Handle


If Time >= #9:28:00 AM# And Time <= #5:32:00 PM# Then
CurrentDb.Execute "qryMasterMinuteTrackingHISTORYupdates"




Exit_err_handle:
Exit Sub


Err_Handle:
MsgBox Err.Description, vbExclamation, "Error Encountered"
Resume Exit_err_handle
End If

[ssanfu]

You have the "End If" in the wrong place.

Option Compare Database
Option Explicit     '<<-- Add this line


Private Sub Form_Timer()

   On Error GoTo Err_Handle

   If Time >= #9:28:00 AM# And Time <= #5:32:00 PM# Then
      CurrentDb.Execute "qryMasterMinuteTrackingHISTORYupdates"
   End If                              '<<-- This is where the "End If" should be

Exit_err_handle:
   Exit Sub


Err_Handle:
   MsgBox Err.Description, vbExclamation, "Error Encountered"
   Resume Exit_err_handle

End Sub

[Micron]

I think you should consider logging the error number and message. I would consider a 'global' sub (a procedure in a standard module) rather than writing the same piece of code over and over in every class module (form and report code is a type of class module). This would accept the error number and message and write that to tblErrors. You can capture other things too, such as the form that raised the error and the user id. However, Access vba has no access to the name of the procedure where the error is raised (which is probably on a lot of people's wish list), your call to the procedure should be unique for each error handler, as in

errHandler:
myErrorLogginProcName (FooBar, and, other, things, here)

Then in the error procedure, use .Execute method and sql statement to append the error, then present the message to the user. A table will also show you how many times an error is being raised.