Handling Bugs

[Frank Wagner]

I develop software for schools. The system is quite an extensive database.
It contains over 100 forms with an average of about 8 printed pages of coding
per form. I distribute the software with a runtime version of Microsoft
Access 2000.

With all these forms and coding, I'm being haunted my minor bugs that keep
popping up now and then that close down the database, so the users have to
restart the system.

I keep fixing the bugs one by one, but it seems like a never ending project.
Most of the bugs are initiated by students interactively doing things I
never imagined they could do.

My question is if I just put in the statement "End Sub" in the Err_XXXX:
subroutines instead of the message statement will that prevent a minor error
from shutting down the system. Is there anything else I can easily do to
reduce the number of terminal problems?

Any help would be appreciated. Thanks

 

[Mike Painter]

I develop software for schools. The system is quite an extensive
database. It contains over 100 forms with an average of about 8
printed pages of coding per form. I distribute the software with a
runtime version of Microsoft Access 2000.

With all these forms and coding, I'm being haunted my minor bugs that
keep popping up now and then that close down the database, so the
users have to restart the system.

I keep fixing the bugs one by one, but it seems like a never ending
project. Most of the bugs are initiated by students interactively
doing things I never imagined they could do.

My question is if I just put in the statement "End Sub" in the
Err_XXXX: subroutines instead of the message statement will that
prevent a minor error from shutting down the system. Is there
anything else I can easily do to reduce the number of terminal
problems?

Any help would be appreciated. Thanks

All research indicates that all software has bugs and at least one major IBM
study claims there is about a 50% chance that fixing a bug will cause or
uncover another one.
(Try Scott Rosenberg's "Dreaming in code" to see you are not alone)

My first suggestion is to see how much code you can get rid of. I wrote a
report card program in Mbasic a "few" years ago for a TRS Mod III and don't
think it ran 8 pages.

If you are looping through records make sure you can't do it with a query.
Whre were you in working with a reltional model when you started this
package?

My attendance package looks like a paper based one, marks week ends and
holidays, has less than 8 pages behind the forms.

 

[Albert D. Kallal]

Well, if you always distribute a mde, then un-handled errors don't shut
down access, and you do not loose any local or global vars.

Remember, one un-handled error and ALL LOCAL + GLOBAL vars will be lost.
This means you application can become quite un-reliable unless EVERY routine
has error handling.

However, while you could put error handling in every singe routine, why not
just use
a mde? Keep in mind that a mde NEVER looses it variables even with NO error
handling
and a error occurs. This just results in a far more reliable application.

And, using a mde also means that access does not shut down either. I never
used the 2000 runtime, but I used 2003 and 2007. In these cases a mde (or
accde) that has a runtime error does NOT force the application to shut down.
I don't believe that the 2000 runtime is any different here.

While I do suggest that you have good error handling, I am of the view that
one does not need to have error handling absolute every routine, especially
when you use a mde.

So, if you are not using a mde, that would likely the best step you could
take to
improve reliability. And, a mde is smaller, and runs faster, and it can't
become un-compiled which will reduce bloat.

 

[Albert D. Kallal]

Are you, in fact, compiling your code prior to distribution? This should
help
pinpoint errors and allow you to correct them before the great unwashed
are
given access.

Keep in mind that while you can compile your code before you distribute it
by going debug->compile from the code menu, keep in mind that if you
distribute a mdb, then any on handled error will cause your to lose all
application to loose all variable values.

More importantly and more specific to this issue, is the posters said that
he's distributing via the runtime and errors are causing his application to
shut down. Keep in mind that any un- handled error in the runtime is a much
worse affair, since it causes the whole thing to SHUT DOWN.

This shutdown occurs right after the users clicks to dismiss the dialog
box that pops up when you got an error.

So while in the regular version of access a un handled error causes you to
blow out all your local and global variables, when it occurs in the runtime,
it not only loses all the variables, when you click OK to dismiss the dialog
box that tells you about the error, the whole access runtime actually
terminates and shuts down.

The user at this point will then have to relaunch the
application and it also possbile they lost some work.

So we're not talking about compiling errors in the code, we are talking
about code without error handling....

My whole point here is that if the user uses a mde, not only do variables
never get blown out, but in the runtime those un-handled errors does not
cause the whole system to shut down.

This 100% shutting down of MS access is normal and typical behavior when you
deploy a regular mdb file with the runtime.So, there is
two significant and important benefits to distributing your
application as an mde.

So, the issue of making sure one's code having compile errors is important,
but not compile errors, but un-handled errors in the runtime that causes
this shutdown.

 

[Allen Browne]

Hi Frank

You've already got some good replies to your question, but there's one
aspect I did not quite follow here.

You say that the bugs "close down the database so the users have to
restart." This should not be the case.

That will happen if you use code without error handling, or if you use
macros (since they don't have error handling.) But you also talk about the
"Err_XXXX:" label, which suggests you do have error handling in your code.
Handled errors should not cause the system to shut down (unless we are
talking about things it can't recover from that keep triggering errors such
as getting disconnected from the network where the data is.)

The short answer to your question is, No: adding "End Sub" in the error
handling is not an ideal way to avoid errors. You certainly need "Exit
Sub/Function" above the error handler, and you need some kind of Resume in
the error handler. The error handler itself needs to be really simple,
without anything that can fail (since nothing handles errors that occur in
its code.)

When using the runtime, it is crucial that every procedure uses error
handling. My suggestion would be to log the errors, so you can see exactly
what's going on, and so address the issues that arise. I think you'll find
this more informative that relying on users to tell you what they did and
what error occurred. Here's the approach I use:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html
Only a couple of times a year do I request a user to send me their error
log, but it can be really helpful.

More broadly, though, if an error is closing the system down, something else
is going wrong. If you are still stuck, give us an example of the kind of
error that's crashing your software.

 

[David W. Fenton]

Remember, one un-handled error and ALL LOCAL + GLOBAL vars will be
lost. This means you application can become quite un-reliable
unless EVERY routine has error handling.

Not true. Not every routine needs an error handler, but every
routine needs to be called in a context in which an error handler is
active. That means a top-level subroutine with an error handler can
call subroutines without them.

My rule is that subroutines with no outside dependencies have no
error handlers. And it works pretty well.

 

[Frank Wagner]

Wow!

I would like to thank everyone for their thoughts. Let me take them one at
a time and see if I can sort it out.

I'm not sure what an mde that Albert refers to is. I distribute the
application using Sage software which packages the Runtime version of the
database so my users don't have to have a full version of Access loaded on
their computers. When an error that is not specifically referred in an error
handling routine occurs. The system gives the user a message that the code
failed and then the database closes. How do I create an mde? Is this a
different form of packaging an access database?

With regard to the error handler coding, my question is if I use the
following code in the error handler will it keep it from shutting down. Two
common errors that I encounter are "Invalid use of null" and "Can't move
focus to control A". Others occur also, but not as frequently. My question
is will placing the following type of general code in the error handler which
merely exits the routine keep it from shutting down. My intent is to maybe
use it for all interactive controls so the user will see that nothing
happened, and they need to use a different response.

Exit_Error:
Exit Sub

Err_Error:
'MsgBox Err.Description 'The Old code which is
dropped

Resume Exit_Error

With regard to the error log, that makes a lot of sense if I can get it to
log the error without shutting down the system. Is there any standard code
to create such an error log?

I appreciate the thoughts and would like to have you all stay with me so I
can come to a workable solution.
--
Frank Wagner
(e-mail address removed)

Hi Frank

You've already got some good replies to your question, but there's one
aspect I did not quite follow here.

You say that the bugs "close down the database so the users have to
restart." This should not be the case.

That will happen if you use code without error handling, or if you use
macros (since they don't have error handling.) But you also talk about the
"Err_XXXX:" label, which suggests you do have error handling in your code.
Handled errors should not cause the system to shut down (unless we are
talking about things it can't recover from that keep triggering errors such
as getting disconnected from the network where the data is.)

The short answer to your question is, No: adding "End Sub" in the error
handling is not an ideal way to avoid errors. You certainly need "Exit
Sub/Function" above the error handler, and you need some kind of Resume in
the error handler. The error handler itself needs to be really simple,
without anything that can fail (since nothing handles errors that occur in
its code.)

When using the runtime, it is crucial that every procedure uses error
handling. My suggestion would be to log the errors, so you can see exactly
what's going on, and so address the issues that arise. I think you'll find
this more informative that relying on users to tell you what they did and
what error occurred. Here's the approach I use:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html
Only a couple of times a year do I request a user to send me their error
log, but it can be really helpful.

More broadly, though, if an error is closing the system down, something else
is going wrong. If you are still stuck, give us an example of the kind of
error that's crashing your software.

 

[Allen Browne]

Regarding error handling, the example on this page covers it:
Error Handling in VBA
at:
http://allenbrowne.com/ser-23a.html
It should *not* close your application down if you are handling the errors.

To help you quickly insert an error handler of your own choosing into any
procedure, consider installing mztools:
http://www.mztools.com/v3/mztools3.aspx

Regarding debugging 'invalid use of Null', see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

Re "can't move focus" errors, there could be several reasons, so the trick
will be to identify the cause and find an alternative approach. Examples of
a possible cause:
a) target control is disabled or not visible
b) validation not met for the current control
c) wrong event (e.g. in BeforeUpate of KeyDown of this control)
d) record cannot be saved (if trying to move into subform)
e) other events are preventing the SetFocus from working.

Regarding an MDB file: it contains 2 versions of the code:
- the text version (what you read and edit);
- the 'compiled' version (semi-machine code that actually runs.)

An MDE contains only the compiled code. Users cannot read or modify the code
if you give them an MDE. They can't switch the forms or reports to design
view either.

To create an MDE in Access 2000 - 2003, go to Tools | Database Utilities.
It's that easy (assuming your code is compilable.) Make sure you keep your
MDB as well: otherwise you won't have anything to edit.

MDEs are not backwards compatible. For a database in Access 2000 format, you
must use Access 2000 to create the MDE, and it can then be used by A2000,
2002, 2003, or 2007. For a database in A2002/3 format, you can create an MDE
using A2002 (usable by A2002, 2003 or 2007) or using A2003 (usable by A2003
or 2007 only.)

After creating the MDE, you will want to set some other properties, e.g. to
prevent the user holding down Shift to bypass the startup, or pressing F11
to see the database window. The code below does that.


Function StartupProps(bSet As Boolean)
Dim dbData As DAO.Database
Dim strDb As String

'Assumes the MDE file is in the same folder _
and has the same name as this one.
strDb = DBEngine(0)(0).Name
If strDb Like "*.mdb" Then
strDb = Left$(strDb, Len(strDb) - 1) & "e"
Else
Debug.Print "NOT SET"
Exit Function
End If

'Open the database
Set dbData = OpenDatabase(strDb)

' ChangeProperty dbData, "AllowBuiltinToolbars", dbBoolean, False
' ChangeProperty dbData, "AllowBreakIntoCode", dbBoolean, False
' Call ChangeProperty(dbData, "AllowFullMenus", dbBoolean, bSet)
ChangeProperty dbData, "StartupShowDBWindow", dbBoolean, False
Call ChangeProperty(dbData, "AllowSpecialKeys", dbBoolean, bSet)
Call ChangeProperty(dbData, "AllowBypassKey", dbBoolean, bSet)

dbData.Close
Set dbData = Nothing
End Function

Function ChangeProperty(dbs As Database, strPropName As String, _
varPropType As Variant, varPropValue As Variant) As Integer
Dim prp As Property
Const conPropNotFoundError = 3270

On Error GoTo Change_Err
dbs.Properties(strPropName) = varPropValue
ChangeProperty = True
Debug.Print strPropName & " is " & varPropValue

Change_Bye:
Exit Function

Change_Err:
If Err = conPropNotFoundError Then ' Property not found.
Set prp = dbs.CreateProperty(strPropName, _
varPropType, varPropValue)
dbs.Properties.Append prp
Resume Next
Else
' Unknown error.
ChangeProperty = False
Resume Change_Bye
End If
End Function

 

[Albert D. Kallal]

I'm not sure what an mde that Albert refers to is. I distribute the
application using Sage software which packages the Runtime version of the
database so my users don't have to have a full version of Access loaded on
their computers. When an error that is not specifically referred in an
error
handling routine occurs. The system gives the user a message that the
code
failed and then the database closes. How do I create an mde? Is this a
different form of packaging an access database?

go:
Tools->database utilities->make mde file

Yes, a mde is a compiled version of your software. Not only does it ensure
your code is compiled, but it also prevents your end users from modify the
forms and even viewing the code. While the runtime can't modify your
code/forms, if that user happens to have the FULL version of ms-access, they
will be able to view and modify your forms if you use a mdb, so one should
use a mde (this prevents users from seeing or modifying your code).

AS MENTIONED, a bonus is that a mde means your ERRORS WILL NOT SHUT DOWN
your runtime application.

I speak about using a mde here:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

 

[Frank Wagner]

Mike:

Thanks for the rule on dependencies. It makes sense to me and I'll try to
use it.

 

[Frank Wagner]

Albert:

Thanks for the information about mde. I had never known of this option. If
you look at my reply to Allen, it was intended for you also.

I've got a lot to absorb now, so let me try a few things and get back to you
guys

Thanks again

 

[Frank Wagner]

Albert:

Thanks. I never knew what mbe meant. I tried it and it works great.

So my plan now is to create an mbe database whenever I want to distribute a
new version, and use that with the Sage software to create distributable
software for my clients.

I will continue development with the mbd database, and convert it to mbe
whenever I want to distribute a new verson. Allen has given me a number of
thoughts about debugging that I also hope to implement.

Just so everyone knows, I always compile my database regularly, and also
compact and repair it before I distribute a new version.

You folks that support this newsgroup are wonderful. Without your help I
probably would have given up trying to develop math software years ago.

Thanks

 

[Frank Wagner]

Allen:

Thanks.

I've tried using mbe and kick myself for not using it earlier. That should
solve a great many problems, including shutting down the database for minor
bugs.

I understand you comments about error handling, and will be working on
making sure errors are logged as a part of the system. Thanks for leading me
to the code for doing that. One question I had is how do you capture the
name of the calling procedure?

Focus control is one thing that drives me crazy. Do you know of any good
source of information for me to study? One problem that haunts me is when a
student enters the wrong answer, the focus doesn't want to return to the same
answer control even if I instruct it to with the "Set Focus" command. I have
to jump through hoops to get it back to the same spot.

As I mentioned to Albert, you folks are great and I really appreciate your
help. Without that help, I would have given up developing software for
students years ago.

By the way, my daughter attended the University of Notre Dame branch in
Perth a number of years ago. She loved the area. It's beautiful.

Thanks again.

 

[Marshall Barton]

Thanks. I never knew what mbe meant. I tried it and it works great.

So my plan now is to create an mbe database whenever I want to distribute a
new version, and use that with the Sage software to create distributable
software for my clients.

I will continue development with the mbd database, and convert it to mbe
whenever I want to distribute a new verson. Allen has given me a number of
thoughts about debugging that I also hope to implement.

Just so everyone knows, I always compile my database regularly, and also
compact and repair it before I distribute a new version.

Just to muddy the waters for you ;-)

Compiling is certainly necessary, but it is not sufficient.
You still have to guarantee that all referenced libraries
are the same on the compiling machine and on all the client
machines. IME, different clients insisted on their own idea
of the "correct" set of libraries so there was no reasonable
way for my machine's libraries to match up. This led me to
adopt the (questionable?) practice of taking a decompiled
program to the client's and doing the final compile on site.
If any client did not manage their systems well enough to at
least have the same libraries on all their machines, then it
was their problem.

 

[Frank Wagner]

Marshall:

That may truely muddy the waters. My clients are schools with older
computer labs. Some are still using Windows 98. There is no way I can take
the system and install it on the computers in the different schools. I
presently am in about 25 schools, and that may grow the 100 in the next year
or two. I mail out new versions yearly for them to install. In addition,
the software is used in over 100 student homes with most computers now having
Windows XP or Vista.

If this is not reliable, I may be best to avoid the solution.

Let me know a little bit more about the problem of libraries if you would -
- and anyone else can join in if need be.

Thanks

 

[David W. Fenton]

Mike:

Eh? I think *I* was the one who mentioned that "rule"...

Thanks for the rule on dependencies. It makes sense to me and
I'll try to use it.

Well, the key problem is determining what an outside dependency is.
In general, a subroutine with strongly typed parameters that has no
calls to any other code is going to be safe. And if it calls code
that has error handlers, that's safe without an error handler, too.

Anything that uses object variables, whether late or early binding,
needs to have an error handler, seems to me, since that is what I'd
define as an outside dependency.

But something like a command button that closes a form doesn't
really need an error handler, seems to me. Well, that all depends --
if you have a Me.Dirty = False in your close code, then you probably
ought to have an error handler, since saving the record is the kind
of thing that can very easily flush out an error that needs to be
handled. An unhandled error in a record save could very easily
result in losing data, while with an error handler, you can return
to the record and correct the problem.

This is one reason why I'm tending towards using a subroutine like
this to close forms:

Public Sub CloseForm(frm As Form, Optional intSaveType = acSaveNo)
On Error GoTo errHandler
If Len(frm.RecordSource & vbNullString) > 0 Then
frm.Filter = vbNullString
frm.FilterOn = False
frm.OrderBy = vbNullString
frm.OrderByOn = False
If frm.Dirty Then frm.Dirty = False
End If
DoCmd.Close acForm, frm.Name, intSaveType

exitRoutine:
Exit Sub

errHandler:
MsgBox err.Number & ": " & err.Description, vbExclamation, _
"Error in CloseForm()"
Resume exitRoutine
End Sub

It's probably overkill for most circumstances, but it means you can
call it with a command button and not worry about having an error
handler in the command button's code.

 

[David W. Fenton]

Two
common errors that I encounter are "Invalid use of null" and
"Can't move focus to control A".

Those kinds of errors are problems in your code, and you should
revise your code so that it handles Nulls (usually by testing if
whatever it is IsNull() before trying to operate on it), and that
you check before moving the focus to a control that can't get focus.

Error handlers should be included for things you *haven't* foreseen,
and the possibility of Nulls and attempting to move focus are both
things you can test for an avoid. It's always better to *avoid* an
error than it is to let the error handler trap for it, partly
because there's sometimes no good fallback at the time the error is
raised. Or, worse yet, trying to fix the problem after you've
encountered the error makes for really complicated spaghetti code.

For instance:

Sub MyControl_AfterUpdate()
Me!MyControl = UCase(Me!MyControl)
End Sub

Now, you could write an error handler that would recover from the
condition where the user deletes the value in the control.

Or, you could just use what some developers call a "guard clause"
that aborts the action if certain conditions are not met:

Sub MyControl_AfterUpdate()
If IsNull(Me!MyControl) Then Exit Sub
Me!MyControl = UCase(Me!MyControl)
End Sub

Some people prefer this:

Sub MyControl_AfterUpdate()
If Not IsNull(Me!MyControl) Then
Me!MyControl = UCase(Me!MyControl)
End If
End Sub

But that can get really messy if the code *after* the guard clause
is quite complex in and of itself. For simple code like the above,
avoiding the Exit Sub is easy. But for code like this:

If ... Then
Select Case ...
Case 1
Case 2
Case 3
End Select
Else
For Each ... In ,..
...
Next ...
End If

It's simpler to stick the guard clause and an Exit Sub at the top of
that:

If [guard condition not met] Then Exit Sub
If ... Then
Select Case ...
Case 1
Case 2
Case 3
End Select
Else
For Each ... In ,..
...
Next ...
End If

than it is to incorporate the guard clause test into the
If/Then/Else structure of the entire code block:


If [guard condition not met] Then
If ... Then
Select Case ...
Case 1
Case 2
Case 3
End Select
Else
For Each ... In ,..
...
Next ...
End If
End If

Now, it's also a good principle to never have more than one exit
point from a subroutine, so instead of Exit Sub you can use a GoTo
exitRoutine:

If [guard condition not met] Then GoTo exitRoutine
If ... Then
Select Case ...
Case 1
Case 2
Case 3
End Select
Else
For Each ... In ,..
...
Next ...
End If

exitRoutine:
Exit Sub

If you've got an error handler, you'll already have an exit label
defined, so a pretty good principle is that it you already *have* an
exit point defined, use it instead of Exit Sub. If you don't, then
you have to decide if your code is sufficiently complex to add in
the exit point, but if your code is complex enough to ask that
question, it's probably complex enough that it should have an error
handler.

More than you ever wanted to know!

 

[Marshall Barton]

That may truely muddy the waters. My clients are schools with older
computer labs. Some are still using Windows 98. There is no way I can take
the system and install it on the computers in the different schools. I
presently am in about 25 schools, and that may grow the 100 in the next year
or two. I mail out new versions yearly for them to install. In addition,
the software is used in over 100 student homes with most computers now having
Windows XP or Vista.

If this is not reliable, I may be best to avoid the solution.

Let me know a little bit more about the problem of libraries if you would -
- and anyone else can join in if need be.

You may(?) be relatively safe if the only libraries you use
are the standard VBA, Access and DAO libraries, which should
be installed as part of the runtime Access. Things might get
tricky if a user installs their own version of Access in the
same directory as your runtime or vice versa, they also use
A2007 (not likely on machines older than Win XP), or if you
use other libraries that can be superseded by other user
installed software. There are some Access SPs and SRs that
can also require a Decompile/Compile.

This issue is not related to your solution. In fact, I
believe, using the runtime helps minimize the problem. I am
certain that others have more experience distributing an
application as widely as you are trying to do, so it would
be nice if they would help clarify things.

 

[David W. Fenton]

One problem that haunts me is when a
student enters the wrong answer, the focus doesn't want to return
to the same answer control even if I instruct it to with the "Set
Focus" command. I have to jump through hoops to get it back to
the same spot.

If you want to prevent leaving the control when something invalid is
entered, you should be validating the data entered in control's
BeforeUpdate event. You would then use the control's .Text property,
which is at that point distinct from the value in the underlying
bound field (because the BeforeUpdate event runs *before* the
underlying bound field is actually updated).

 

[David W. Fenton]

Compiling is certainly necessary, but it is not sufficient.
You still have to guarantee that all referenced libraries
are the same on the compiling machine and on all the client
machines. IME, different clients insisted on their own idea
of the "correct" set of libraries so there was no reasonable
way for my machine's libraries to match up. This led me to
adopt the (questionable?) practice of taking a decompiled
program to the client's and doing the final compile on site.
If any client did not manage their systems well enough to at
least have the same libraries on all their machines, then it
was their problem.

I think that you really oughtn't need to do that if you use late
binding for everything but the default Access libraries. The only
thing that would then cause it to fail would be something like the
DAO DLL not being registered, and compiling on that machine wouldn't
fix that, in any case.