Mystery Access VB bug

  • Thread starter Thread starter Sam Smith
  • Start date Start date
S

Sam Smith

I wonder if I am going insane. Across all recent versions
of Access I seem to have come across the following bug and
I just thought I would mention it to see if there is any
interest from MS to actually fix it.

The weird bug:

Every so often in forms coding I eventually get to a point
where I get an error in the same way as if I had a missing
reference but it has, in fact, nothing to do with that at
all except appears to be some sort of mystery internal
Access bug. The latest occurrence of this bug in detail:

I have a form with a sub form. On the event on one of the
text boxes in the sub form I have some simple code which
activates when the text box is double-clicked.

This is all working fine until the mystery internal bug
appears.

In changing some code I decide to make one variable
public. This then seems to break the double-click code. If
I remove the new variable and put the code back to how it
was the bug is still there. Every time I double click on
the text box in the subform I get the usual error that I
would get if I had a missing reference.

But it gets weirder (and more annoying).

If I import over the SAME CODE (identical in everyway)
from a backup - it works fine. If I try to run the old
code then it brings up the reference error. 2 pieces of
code that are identical to the Access developer but
internally must look different to Access.

Now, there is obviously a lot going on behind the scenes
in Access that us developers are not privy to and it is
clearly that something in the internal code of Access
itself is screwy. I have used Access since '97 and have
come across this weird reference bug in different guises
off and on since then. It doesn't happen very often -
perhaps once every 6 months. I do use Access every day so
perhaps only a few of us will trigger this.

Although I keep getting around it I just thought that it
might be nice if MS could be made aware of it and release
a patch - because when it occurs it is a real pain to
debug as it is not actually anything a normal developer
can fix as it is internal to Access.

If this could be fixed then it would make Access even more
perfect! :)
 
Hi Sam

I hope you get several replies, as this kind of thing is hard to diagnose.

From your description, it sounds like your mdb is becoming partially
corrupt. The fact that the problem can be fixed by replacing the code with
identical code reinforces that view.

Access keeps 2 copies of your code: the text version (what you edit), and
the compiled version (what actually runs). While you are modifying forms and
their modules, it is quite common for Access to get these two out of sync,
and you then have a corruption.

IME, one of the things that triggers this corruption is editing the module
of a form in break mode. That is, you open a form (not design view). An
event is triggered. Your code runs, and hits a bug. You choose to Debug, and
are dropped into the VBA window with the problem line highlighted. At this
point, if you edit the code, Access has to create a temporary copy of the
form and the code (as it always does when you start an edit) so that it can
revert to the previous saved state if needed. So, it is now trying to juggle
four copies of the code: text and compiled of original and temporary, and
some of these are being created while some are running. Somewhere in this
process, it gets the copies confused.

Occassionally you can actually catch it out: even though you have altered
the text version of the code visible in your editing window, the Compile
option on the Debug window sometimes remains greyed out (assuming you were
fully compiled at this point), i.e. Access still thinks the project is
compiled, even when you have altered the code. Another indication is that it
can zip straight past a breakpoint without stopping. The compiled version it
is using is out of sync with the text version.

Once we realized what was going on, we made a conscious decision never to
edit in break mode, but to always switch the form to design view before
editing the module of the form. This was in Access 97, and our corruptions
during development dropped dramatically.

For other suggestions on preventint corruption, see:
http://members.iinet.net.au/~allenbrowne/ser-25.html
#2 and #3 do not apply to Access 97, but the other ideas may help.
 
I agree with Allen that this is most likely a corruption
issue. As he explained, there probably is a bug involved,
but it's incredibly convoluted. I'm certain that MS will
fix it as soon as they can pin down the cause.

Here's another link that may help clear up the problem in
your mdb:
http://www.granite.ab.ca/access/corruptmdbs.htm
 
Thanks for your reply.

What you say makes sense. I too try to avoid doing
anything that will stress it out by not editing in break
mode.

It would be nice to think that one day MS will actually
spend some time curing these bugs as without them Access
would be so much better for developers. As I have used
Access over the years I do come across all sorts of odd
bugs. It is a shame there is not some sort of 'Bug Track'
to post them too and watch their progress as they are
being fixed ready for the next SP or version.
 
Great.

Because A97 is out of support, I'm sure MS is not interested in fixing it
any further.

When the newer versions crash, they offer to send a report to Microsoft.
Based on those reports, MS has been working to eliminate the crashes. Access
2003 is now *much* more stable than A97 ever was. In our experience, while
developing forms and code in A97, we would expect 2 crashes a week, whereas
in A2003, we expect a crash about once every 2-3 weeks. That's almost an
order of magnitude improvement.

There are still heaps of non-crashing bugs in A2003, but there is no
question that it is more stable. In my view, the bugs that cause Access to
silently yield incorrect results are worse than the crashing bugs, because
you do not *know* that something is wrong. We have listed those at:
http://members.iinet.net.au/~allenbrowne/tips.html#flaws
 
Every time I double click on
the text box in the subform I get the usual error that I
would get if I had a missing reference.

When the above happens...can you compile your code?

Also, remember, that if you change the data source of a from, then you can't
use:

me.MyContorlName

You have to use

me!MyContorlName

The first one works for forms that have the sql, or data source set.
However, if you change the data source via code, then *IF* the control is
NOT on the screen, then me.MyFeildValue will FAIL. You code will NOT all of
a sudden compile. So, the solution is to either place a text box on the
screen, or as mentioned used me!MyFeildValue.

So, if you don't have correspond controls on the screen, and you change the
data source...you can't use me.MyFieldName.

However, as mentioned, it sounds like a corruption on your part.

My only question to the above is does the code compile before you run it?
 
My point with the bug was really to see if we could get
some support for MS to fix the internal issues with Access
as these problems have been here for years.

BTW the code does indeed compile fine without errors. The
bug is an internal Access one.

Also today I've managed to find a piece of SQL that
guarantees crashing Access 2003 (not sure if it works on
older versions). Here it is:
SELECT [qryOccupancyAll].[WeekNo], (SELECT Sum
([qryOccupancyAll].[CountOfVehicleID]) AS Total
FROM [qryOccupancyAll]
WHERE ((([qryOccupancyAll].[WeekNo])<=[tblAlias].
[WeekNo]));) AS Total
FROM qryOccupancyAll AS tblAlias;

It would be good if MS could have a bug forum somewhere.
 
Also today I've managed to find a piece of SQL that
guarantees crashing Access 2003 (not sure if it works on
older versions). Here it is:
Click to expand...
SELECT [qryOccupancyAll].[WeekNo], (SELECT Sum
([qryOccupancyAll].[CountOfVehicleID]) AS Total
FROM [qryOccupancyAll]
WHERE ((([qryOccupancyAll].[WeekNo])<=[tblAlias].
[WeekNo]));) AS Total
FROM qryOccupancyAll AS tblAlias;
Click to expand...

It looks like the above is executing on another query? If yes, then the
above query really means very little, and would be of little use in tracking
down this problem (if in fact the above is executing on another query, then
you are being VERY silly here to leave out the other query).

The source of that other query would have to be shown. That query could have
VB functions and add kinds of stuff here . (my bets are that
qeryOccupanceAll has some VB expressions here..but even it does not, to
track this down, we would certainly have to look at the other query
(assuming that this is a query on a query.. To leave this out is like saying
that you took a rope....but forgot to tell me that you got a cow attached to
the rope!!).

If you can put 2 or 3 sample records in a table, and take the above query
that does not work (test it to ensure it don't work), and then email it to
me. Doing the above should only take you less then 1 minute of your time.
Send it to me, and I will be glad to look into why the query don't work. If
it is a bug..then lets get it looked at...
 
Back
Top