Microsoft Access 2010 Bible

Chapter 21: Building Multiuser Applications

775

Tip
To keep this error from occurring altogether, try to keep users from starting more than one instance of your
application. You can do this using the FindWindow API in your start-up routine. Check for a running instance
of Access at start-up, and close the second instance if another is present.

The error handler called by both of these examples is really very simple. It merely reads the current

error and uses a Select Case statement to decide what to do. The only errors I’ve included in

this module — modErrorHandlers — relate to this chapter. You’ll notice that the

ErrorRoutine function has entries for Error 3186 and Error 3260. Each of these calls another

subroutine, ParseError. ParseError (in module modParseError) accepts the error number

and error message as arguments, and parses the user name and the machine name from Error 3260

and Error 3186. It doesn’t matter which error has occurred because the parsing routine looks only

for the “user” and “ on machine” string values within the error message, so it’s pretty generic.

In fact, this same subroutine is called from both errors. You can make the default Access error mes-

sage more descriptive, and you can also make use of the user ID and machine number within your

error log (if you keep one). The code follows:

Public Sub ParseError(lngErr As Long, strError As String)

Dim strUser As String

Dim strMachine As String

Dim lngUserStart As Long

Dim lngMachineStart As Long

Dim lngMachineEnd As Long

lngUserStart = InStr(1, strError, “user”) + 5

lngMachineStart = _

InStr(lngUserStart, strError, “ on machine”)

lngMachineEnd = InStr(lngMachineStart, strError, “.”)

strUser = Mid$(strError, lngUserStart, _

lngMachineStart - lngUserStart)

strMachine = Mid$(strError, lngMachineStart + 12, _

lngMachineEnd - (lngMachineStart + 12))

MsgBox “The Record Could Not Be Locked “ _

& “Because It Is Locked On “ _

& strMachine & “ By “ & strUser

End Sub

Error 3197: Data has changed; operation stopped

Error 3197 can be one of the most confusing errors to an end user if it isn’t captured through a

VBA error handler. It usually occurs when optimistic locking is enabled in an application, but it

may also occur in an environment containing mixed record-lock settings as well.

Here’s an example: Mike starts an edit on his machine. During the course of the edit (Mike is a

slow typist), Elizabeth starts and finishes an edit on the same record. This means that the underly-

ing data that Mike is editing is no longer valid, so an error is flagged when he tries to save his

changes. The resulting Access error message box is shown in Figure 21.8.