should I really have to be the squeaky wheel?

[Guest]

I have asked this question twice in the last week hoping for some help, but
instead I get absolutely nothing, not even a comment or question regarding
further information needed.

Meanwhile other questions that people could easily answer themselves by
using Access' built-in Help files are answered here so quickly it makes my
head spin, which of course looks really funny but also makes me nauseous
because, to me, this is the place to come when the answers aren't readily
available elsewhere.

So I begin to wonder if I'm doing something wrong...

Then I search for the word "spell" to see what I can find, and I come across
a rather long-winded ranting commentary dated March 2005 from some dude
comparing Access to a nice-looking car that's actually a big fat P-O-S.

That post, which didn't even present a real question about a specific
problem, had THIRTY-SIX (yeah that's right, 36!!) replies, where volunteer
users and MVPs alike went back and forth with the guy...

But I can't get even ONE response to this question(??):

------------------------------------------------------------
In Access 2000 db with SQL linked tables, I have spell check set up as public
function Spellcheck() with "DoCmd.RunCommand acCmdSpelling" command line in
Visual Basic.

The function is usually tied to a command button on the footer of a form.
Clicking the button sets the focus on the first text field of the
form and then runs the public function Spellcheck(), which in turn cycles
through the form fields to check spelling.

The problem is that sometimes Spellcheck causes the form to move to a new
blank record, even though the form is set to Cycle:Current Record only.

Unfortunately, "sometimes" can't be pinpointed because it changes from one
day to the next, from one form to the next, under some of the same
conditions, etc. (a lot like what the car guy was talking about!)--sometimes
it happens when there are no spelling errors, sometimes when there are and I
don't cancel the procedure, and sometimes it doesn't happen at all... and all
of these conditions are in reference to the same form!

Is there anything I can do to ensure that spell check consistently won't
make forms move to new blank record (or is consistency a pipe dream in our
little Access world)?

Help would be appreciated, thanks.
------------------------------------------------------------

If anything here is unlcear, please ask me to clarify. If it sounds like
this shouldn't be happening, please say so. At least that would be some kind
of affirmation so I know I'm not just crazy... although sometimes I do think
Access should come with a warning label that extended use can cause insanity.

 

[Dirk Goldgar]

I have asked this question twice in the last week hoping for some
help, but instead I get absolutely nothing, not even a comment or
question regarding further information needed.

Meanwhile other questions that people could easily answer themselves
by using Access' built-in Help files are answered here so quickly it
makes my head spin, which of course looks really funny but also makes
me nauseous because, to me, this is the place to come when the
answers aren't readily available elsewhere.

So I begin to wonder if I'm doing something wrong...

Then I search for the word "spell" to see what I can find, and I come
across a rather long-winded ranting commentary dated March 2005 from
some dude comparing Access to a nice-looking car that's actually a
big fat P-O-S.

That post, which didn't even present a real question about a specific
problem, had THIRTY-SIX (yeah that's right, 36!!) replies, where
volunteer users and MVPs alike went back and forth with the guy...

But I can't get even ONE response to this question(??):

------------------------------------------------------------
In Access 2000 db with SQL linked tables, I have spell check set up
as public function Spellcheck() with "DoCmd.RunCommand acCmdSpelling"
command line in Visual Basic.

The function is usually tied to a command button on the footer of a
form. Clicking the button sets the focus on the first text field of
the
form and then runs the public function Spellcheck(), which in turn
cycles through the form fields to check spelling.

The problem is that sometimes Spellcheck causes the form to move to a
new blank record, even though the form is set to Cycle:Current Record
only.

Unfortunately, "sometimes" can't be pinpointed because it changes
from one day to the next, from one form to the next, under some of
the same conditions, etc. (a lot like what the car guy was talking
about!)--sometimes it happens when there are no spelling errors,
sometimes when there are and I don't cancel the procedure, and
sometimes it doesn't happen at all... and all of these conditions are
in reference to the same form!

Is there anything I can do to ensure that spell check consistently
won't make forms move to new blank record (or is consistency a pipe
dream in our little Access world)?

Help would be appreciated, thanks.
------------------------------------------------------------

If anything here is unlcear, please ask me to clarify. If it sounds
like this shouldn't be happening, please say so. At least that would
be some kind of affirmation so I know I'm not just crazy... although
sometimes I do think Access should come with a warning label that
extended use can cause insanity.

I'm afraid you'll have to face the fact that sometimes a question just
falls through the cracks, even when someone currently in the newsgroup
knows the answer. Questions that are badly defined -- which yours
isn't, don't get me wrong -- or that no one knows the answer to, may get
no reply. And some posts may arouse the attention of a lot of people,
whether they are questions or not.

I don't know exactly what's causing your problem, but if you'll post the
code for your Spellcheck() function, maybe something will become clear.

 

[Guest]

(see below)

I'm afraid you'll have to face the fact that sometimes a question just
falls through the cracks, even when someone currently in the newsgroup
knows the answer. Questions that are badly defined -- which yours
isn't, don't get me wrong -- or that no one knows the answer to, may get
no reply. And some posts may arouse the attention of a lot of people,
whether they are questions or not.

I don't know exactly what's causing your problem, but if you'll post the
code for your Spellcheck() function, maybe something will become clear.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Hi Dirk, and thanks for responding so quickly!

I did put the code in the question, but let me rephrase it in Access-ese:

********************
Public Function SpellCheck()

DoCmd.RunCommand acCmdSpelling

End Function
********************

It's just the DoCmd version of spell check--nothing fancy.

And to further explain the "sometimes" problem, I just tested it again on
the same record that prompted my original post here, and it works just fine.
Last week, on the same form showing the same record, it moved the form to new
blank record.

Like the car guy, I struggle with the same kind of weird phantom issues
where things just suddenly don't work anymore, and I distribute .mde files
for my people too. This spellcheck problem is one of the most troublesome
because immediately after it runs, the program prints the form to PDF, so I'm
sure you can imagine that printing a blank form isn't what's intended ever.

The print to PDF function is another issue because it puts a document name
in the form's caption so users don't have to name the document that is
produced and it conforms to our company's naming convention in preparation
for going paperless. For (only some of) my interoffice .mde users, this
works only once to name the doc, but then the next time they have to print to
PDF, the doc name doesn't show in the Save As box (but it does show correctly
in the form's caption). I realize this part probably doesn't belong in
Access forms coding forum, but just giving you an illustration of the
weirdness.

My design copy almost never reflects the problems my office users complain
about, and we have the same .mde file on Citrix for our field users, which
always works flawlessly. Now my design copy has started having the
spellcheck and print to PDF problems, and I need to make sure not to pass
problems on to users in the .mde files.

The only thing that ever changes on my computer are Windows updates, but
these shouldn't affect MS Office, right?? I think we have ghosts in the
machine...

Any comment is greatly appreciated, even if it's only to tell me I'm fubar.
Thanks!

 

[Dirk Goldgar]

(see below)
Hi Dirk, and thanks for responding so quickly!

You mean "eventually", don't you?

I did put the code in the question, but let me rephrase it in
Access-ese:

********************
Public Function SpellCheck()

DoCmd.RunCommand acCmdSpelling

End Function
********************

It's just the DoCmd version of spell check--nothing fancy.

You might have done various other things in the routine, so I had to
make sure.

And to further explain the "sometimes" problem, I just tested it
again on the same record that prompted my original post here, and it
works just fine. Last week, on the same form showing the same record,
it moved the form to new blank record.

I haven't used the spell checker much, but my understanding and limited
experience is that it behaves differently depending on what, if
anything, is selected at the moment it is invoked. If nothing is
invoked, it usually (I think) spell-checks the whole form, meaning all
records on the form -- and that can lead to a change in the current
record. If your purpose is just to check all fields in the current
record, see if you get the results you want with this small modification
to your function:

'----- start of code -----
Public Function SpellCheck()

Application.RunCommand acCmdSelectRecord
Application.RunCommand acCmdSpelling

End Function

'----- end of code -----

Note: there's not really any functional difference between
"DoCmd.RunCommand" and "Application.RunCommand". Most, if not all, of
the methods that were originally provided via the DoCmd object have
since been added to the Application object, so I generally use that.

Like the car guy, I struggle with the same kind of weird phantom
issues where things just suddenly don't work anymore, and I
distribute .mde files for my people too. This spellcheck problem is
one of the most troublesome because immediately after it runs, the
program prints the form to PDF, so I'm sure you can imagine that
printing a blank form isn't what's intended ever.

In my experience, most Access behavior that is seen by the user (or
developer) as erratic or flaky is the result of not understanding what
is really going on in the application, or else due to a complex
interaction of events. That's not to say that I haven't seen odd
behavior that is the result of bugs in Access! But I have seen very
little behavior that can't be reproduced consistently, once the
circumstances that cause it are understood. Exceptions to this are
usually due to timing issues that may vary based on circumstances that
are outside the user's control. Still, it usually pays to first try to
get a deeper understanding of exactly what the application is doing.
Even if that effort turns up a true bug in Access, the level of
understanding usually suggests a workaround.

Now I apologize for lecturing.

The print to PDF function is another issue because it puts a document
name in the form's caption so users don't have to name the document
that is produced and it conforms to our company's naming convention
in preparation for going paperless. For (only some of) my
interoffice .mde users, this works only once to name the doc, but
then the next time they have to print to PDF, the doc name doesn't
show in the Save As box (but it does show correctly in the form's
caption). I realize this part probably doesn't belong in Access
forms coding forum, but just giving you an illustration of the
weirdness.

I can't comment without knowing more about the process and seeing the
code involved.

My design copy almost never reflects the problems my office users
complain about, and we have the same .mde file on Citrix for our
field users, which always works flawlessly. Now my design copy has
started having the spellcheck and print to PDF problems, and I need
to make sure not to pass problems on to users in the .mde files.

The only thing that ever changes on my computer are Windows updates,
but these shouldn't affect MS Office, right?? I think we have ghosts
in the machine...

One thing to watch out for is the possibility of corruption in your VB
project. That is blamed for more things than I think it's usually
responsible for, but I'd certainly go through a process wher I compact
my database, decompile it, compact it, and recompile it, before creating
an MDE. All that should only be done after a good backup copy has been
taken and verified, of course -- better yet, two copies.

 

[Guest]

You mean "eventually", don't you?

Haha, well I guess if you consider the previous 6 days' waiting, then yes,
but your response to THIS thread was very quick. That was nice, thanks.

If your purpose is just to check all fields in the current
record, see if you get the results you want with this small modification
to your function:

Public Function SpellCheck()

Application.RunCommand acCmdSelectRecord
Application.RunCommand acCmdSpelling

End Function

Done, and thanks for the tip. I hope this solves the infrequency and
unpredictablilty of the weirdness (for spellchecker anyway)...

In my experience, most Access behavior that is seen by the user (or
developer) as erratic or flaky is the result of not understanding what
is really going on in the application, or else due to a complex
interaction of events. That's not to say that I haven't seen odd
behavior that is the result of bugs in Access! But I have seen very
little behavior that can't be reproduced consistently, once the
circumstances that cause it are understood. Exceptions to this are
usually due to timing issues that may vary based on circumstances that
are outside the user's control. Still, it usually pays to first try to
get a deeper understanding of exactly what the application is doing.
Even if that effort turns up a true bug in Access, the level of
understanding usually suggests a workaround.

Now I apologize for lecturing.

I have been programming with Access for 10 years, the last 4 of which it has
been my job exclusively. I say this to let you know that there is very
little I don't understand about what is going on, especially when I am the
one writing the code My troubleshooting skills are extensive; when I
come here with a problem like this, it is because I can't reproduce it
consistently or at all... and I don't come here much, like less than once a
year.

I think "complex interaction of events" best explains the weirdness, but not
entirely. For example, our office is split between two floors of the
building, and my users upstairs frequently have problems that my users
downstairs don't. I have logged on as upstairs user on downstairs computer,
and the problems vanish.

They all use the exact same copy of the .mde file, but each has their own
individual copy residing on their machine. This makes me think the problem
must be caused by differences in the machines because we all use the same
version of Office. I'm sure you can see my befuddlement with this, but even
more so when the same problem doesn't happen twice on the same record, same
form, same function, etc. even on the same computer!

I can't comment without knowing more about the process and seeing the
code involved.

Basic rundown: Print to PDF button is clicked--spellcheck function
runs--caption of form is changed to incorporate some of the form's info in
the name--Print to PDF function runs. PDF function changes user's default
printer to Adobe PDFwriter, prints (which causes Save As dialog box for
telling it where to save the PDF file, named by the form's caption), then
changes user's default printer back to what it was initially.

I wasn't really expecting you to comment on this part, it was more or less
just FYI to further illustrate the weirdness with how something doesn't work
consistently upstairs, but works fine downstairs and on Citrix every time.

One thing to watch out for is the possibility of corruption in your VB
project. That is blamed for more things than I think it's usually
responsible for, but I'd certainly go through a process wher I compact
my database, decompile it, compact it, and recompile it, before creating
an MDE. All that should only be done after a good backup copy has been
taken and verified, of course -- better yet, two copies.

I am pretty good about compiling regularly, but I will try to be more
diligent and see if that helps.

Thanks for all the info, Dirk. I really appreciate your help.

Sandy

 

[Albert D.Kallal]

Done, and thanks for the tip. I hope this solves the infrequency and
unpredictablilty of the weirdness (for spellchecker anyway)...

Have you ever tried using the spell checker in ms-access from the menu?

I just tried the spell checker (a feature I never used by the way...so I am
participating here to learn too!!).

By giving the spell checker a try. (I just opened up a form, and then went
tools->spell checker), I have discovered that it is NORMAL behavior for
ms-access to move from one record to the next. So, at first glance, and
first use, spell checking operates on all records.

So, if a user clicks on a mouse, or bumps a key, and your selection goes
away..then the spell checker is going to start running through all records
in your database. You might have code to cycle through some controls on your
form, but if a user clicks on something, you loose the select, and then the
spell check is going to go on a wild ride. Also, what happens when the text
control is empty..there is NOTHING to select, and again the spell checker is
thus going to operate on the WHOLE form, and even try to move to the next
record. This means that EVERY time you fire off the RunCommnand
acCmdSpelling, you run the risk of it running away..and trying to move to
the next record.

In fact, I never used spell check in ms-accede ever. However, in reading
your post, I went, hum...I wonder how spell check behaviors. I fired up a
form, went tools->spell-check..and sure enough..the NATURAL use of spell
check causes navigation through ALL records. Based on my small efforts to
just TRY the speller checker, one has to INSTANTLY conclude that a MAJOR
problem to solve here is going to be how to stop record navigation by use of
that option.

As mentioned, if nothing is selected, then all records are open hunting game
for the spell checker. So, do you execute the command when the text box is
empty, or nothing is selected?

In fact, after trying spell checking (for my first time), my first
conclusion, and first question would be how on earth am I going to get this
feature not to move around..and stop it from jumping from record to record?

The idea of selecting the current record by Dirk seems like a good idea, and
should help.. However, in looking at this feature, I would not be sleeping
all that easy here.

So, you should make sure you code checks if the text box HAS some text in it
BEFORE you fire off the spell checker (think about this...no text in a
control means that NO selection can occur, and you are right back to that
DANGER DANGER WILL Robinson problem of the checker running off...

I don't know if the spell checker object can be used with methods and
properties. So, a great subject for another question by the way would be to
ask that very question!

eg: If I was looking for even more reliability here, then I would dump the
use firing off the spell checker since it is one of those things that TRIES
to operate on everything in sight! What I would do is pull the text from
each control, and then PASS this text to a separate routine that can spell
check the text, and can do so without even having to use the
acCmdSpellcheck, as that is just a option for users, and hard to control
via code. I don't know if this possible right now, but my first question is
can the spelling object be used in ms-access?

 

[Dirk Goldgar]

I think "complex interaction of events" best explains the weirdness,
but not entirely. For example, our office is split between two
floors of the building, and my users upstairs frequently have
problems that my users downstairs don't. I have logged on as
upstairs user on downstairs computer, and the problems vanish.

They all use the exact same copy of the .mde file, but each has their
own individual copy residing on their machine. This makes me think
the problem must be caused by differences in the machines because we
all use the same version of Office. I'm sure you can see my
befuddlement with this, but even more so when the same problem
doesn't happen twice on the same record, same form, same function,
etc. even on the same computer!

Hmm. It seems to me that the most likely candidates for this sort of
problem, given that everybody's using an MDE, are

(a) library differences among different computers,

(b) network hardware and configuration differences, which can cause
timing differences and locking differences, and

(c) network permissions differences.

If all computers are kept completely uniform as far as OS and Office
service levels are concerned, I wouldn't think (a) would be a factor.
The fact that you particularly distinguish the upstairs users from the
downstairs users suggests that it may have something to do with the
network. Unfortunately, networking isn't an area I'm all that familiar
with.

Basic rundown: Print to PDF button is clicked--spellcheck function
runs--caption of form is changed to incorporate some of the form's
info in the name--Print to PDF function runs. PDF function changes
user's default printer to Adobe PDFwriter, prints (which causes Save
As dialog box for telling it where to save the PDF file, named by the
form's caption), then changes user's default printer back to what it
was initially.

The process certainly sounds reasonable. Are you working with the
Printer object to do this? I've heard recently of a few problems with
it, but I haven't ever done anything with it.

I wasn't really expecting you to comment on this part, it was more or
less just FYI to further illustrate the weirdness with how something
doesn't work consistently upstairs, but works fine downstairs and on
Citrix every time.

Of course it works on Citrix -- the Citrix users are all running on the
same server. I wouldn't be at all surprised to find that it's a network
timing issue.

Windows updates could affect Jet, the database engine, since that's now
been lumped in with the OS.

I am pretty good about compiling regularly, but I will try to be more
diligent and see if that helps.

Does "compiling regularly" include decompiling first? (again, only with
a good backup in hand)

Thanks for all the info, Dirk. I really appreciate your help.

Inconsistent behavior is the hardest to debug, of course, but I'm happy
to give what help I can.