Question for Tom Wickerath re automatic messages

[CW]

Tom -
Thanks for all your help yesterday with the auto messages to be sent via
Outlook when logging into or out of Access. I'm intrigued to see that it has
spawned quite a lengthy discussion!
However, although it seemed to be OK in quite a bit of testing, I have now
found a problem - unfortunately, only after I had deployed this up to the
office server and advised all the users how it works. This is it:
When the proposed message appears upon exiting (clicking to close the
Switchboard form) , everything is great if you go ahead and hit Send.
However, if you choose not to, for some reason, and close the message
window, Access then becomes rather upset or confused, or both.
A dialogue box appears saying:
"You can't exit now - if you're using a VB module that is using OLE or DDE,
you may need to interrupt the module".
Well, a chance would be a fine thing - everything is locked solid - the
switchboard form, the menu bar, the only thing that responds in any way at
all is the top right X to close all of Access. Hit that and what do you get -
the same error message back again.
The only option seems to be Task Manager / End Task (Access).
And if you do that, you're then left with an active Lock file so you can't
get back in again!
Ouch....some advice asap, please, cos I'm sure it won't be long before one
of the users chooses not to send one of the messages and the whole thing will
go pear-shaped.
Many thanks
CW

 

[Tom Wickerath]

Hi CW,

... only after I had deployed this up to the office server and advised all the
users how it works.

It sounds to me like your application may not be split into what we commonly
refer to as a front-end (FE) and back-end (BE) files. Each user should have
their own copy of the FE application on their local hard drive; only the BE
..mdb file (contains tables only) is shared between users. For more
information, please see the following document:

Implementing a Successful Multiuser Access/JET Application
http://www.access.qbuilt.com/html/multiuser_applications.html

The second paragraph includes a clickable hyperlink where you can learn more
about the benefits of splitting an application. In addition, here is another
link where you can learn more about splitting:

http://home.bendbroadband.com/conradsystems/accessjunkie/splitting.html

Earlier, I stated:

For leaving work, you can send a similar message via a saved macro (or VBA
code, if you are comfortable working with VBA). Just call the macro or code
in the Close Event procedure of your switchboard form.

Did you use a saved macro or VBA code? If you used a macro, the result of a
user cancelling the Send would throw an error. However, one cannot trap for
errors in Access 2003, and any previous version. (One can trap for errors in
macros in Access 2007). For this reason, I tend to avoid using macros like
the plague. Consider the following quote regarding the use of macros:

<Begin Quote>
From: "Inside Relational Databases, 2nd Edition, by Mark Whitehorn and Bill
Marklyn, published by Springer, p 151)

"Macros offer the next level down, extending the functionality of the GUI.
Macros are still limited, however, and do not provide anything like the
enormous flexibility of a programming language. Both the macro and the
programming languages take some effort to learn and, surprisingly, often
require relatively different skills; in other words, a good working knowledge
of macros may not make it much easier to convert to using the programming
language. Perhaps even more surprisingly, I do not believe that programming
is fundamentally more difficult to learn. Macros are easier to use but not by
orders of magnitude."

"If you are new to RDBMSs, I suggest (with as much deference as possible)
that you may well not be in a position to judge whether you need macros or
programming. In that case, my advice is clear. Unless you are sure that your
needs really are simple, don't bother learning to use macros. Once you find
that you need more than the GUI offers, go straight to the programming
language. In this way you avoid the pain of climbing one learning curve only
to discover that the view from the top is unsatisfactory and another climb
awaits you."

<End Quote>

Even though macros have been extended significantly in Access 2007, to me it
just means that the mountain is taller; in other words, you likely will still
find that the view is unacceptable after climbing to the top!

If you use VBA code, you can trap for run time errors and handle them
gracefully. This might include simply ignoring them, in the case of a user
who cancles sending a message from Outlook. You'll need to learn the
appropriate error numbers first, so that you can then add them to a SELECT
Case....End SELECT (or IF...Then End If) construct. Looking at some of my
past work, I see, for example, the following:

ProcError:
Select Case Err.Number
Case 2501, 2293, 2296 'User cancelled message (2293 & 2296 are raised
by Outlook, not Outlook Express).
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdSendDetailEmail_Click..."
End Select
Resume ExitProc
End Function


In another application, which involved automating Outlook, I had to trap for
errors 287 and 429:

ExitProc:
Set myItem = Nothing
Set myObject = Nothing
Exit Function
ProcError:
Select Case Err.Number
Case 287 'User clicked No on prompt presented by Outlook.
SendMail = "Action cancelled by user."
Case 429
SendMail = "Outlook is not running; Please start Outlook first."
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in SendMail Function..."
SendMail = "A problem was encountered attempting to automate
Outlook."
End Select

Resume ExitProc
Resume
End Function


So, if you used a macro, try converting it to VBA code. You can select the
macro in question, and then click on Tools > Macro > Convert Macros to Visual
Basic. Note that macros will always be converted to functions (not
subroutines). Also, the VBA editor may open to a different module versus the
one that was just created when you convert the macro. But, once you have the
macro converted, you can add the appropriate error handling to trap for
specific error numbers and respond accordingly.

A bit unrelated to this discussion is a word document that I have made
available called "Access Links.doc". A zipped copy is available here:

http://home.comcast.net/~tutorme2/samples/accesslinks.zip

This document includes lots of useful tips for people who are working with
Access. I consider the first three pages very important, along with the two
hyperlinks shown in red font on page 4. The rest of the document contains
information that is more in the 'nice-to-know-about' catagory.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[CW]

Tom -
My goodness, that's an incredible reply!
I've been working in Access for a little under a year now and have been
picking the brains of this Group on a fairly regular basis and it never
ceases to amaze me how people like you come up with your expertise, providing
not only the solution to a problem but a full explanation of the whys and
wherefores, the pros and cons of the different approaches that might be
possible.
Yes I did use macros, but I shall certainly try the VB route now.
And you are right (of course!) about splitting the db. We have been running
it for a number of weeks now and I have been hovering over the decision to
split or not to split, putting it off as long as possible as it seemed to be
just another potential source of trouble (if it ain't bust don't fix it, was
my strategy) but I think you have now made up my mind for me.
I'm most grateful for the links you provided - a wealth of information.
THANK YOU SO MUCH!
CW

 

[Tom Wickerath]

Hi CW,

You're very welcome. Glad I could help.

Good luck on your project.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________