Wierd issue with vanishing text

[topher23]

I'm having the strangest issue of invisible text in Access 2003.

I have several unbound textboxes that reference fields in a subform. Once upon a time, they all worked fine, but now they've developed some really peculiar behavior. The text boxes are all blank until the user mouses over one specific text box that evaluates a field in the subform with IIf and displays either the data from the subform or N/A. As soon as the mouse hits that text box, all of the text in the unbound text boxes magically appear!

Unfortunately, the text is all static - if the user changes something in the subform or switches records, the text in the main form remains the same.

Although the data entry folks are responding to this odd behavior with bemused laughter, I'd like to know what the heck is causing the problem. Cutting and pasting the text box controls doesn't help, nor does deleting the magic mouse-over control.

I was looking at prn and Adezii's postings here: (weird-version-dependency-problems)
which are equally wierd text field invisibility issues, but don't provide any answers. :(

[ADezii]

Hello topher23, any chance of Attaching the DB or a segment of it?

[topher23]

Okay, I was finally able to get it to work as a part. I set up the offending form as the startup form. The field that you mouse over is "Difference" up in the header area of the form.

After putting it together like this, suddenly the Shift Length field works properly, but still, none of the other fields work until mousing over the Difference text box. Odd.

Please ignore any ugly code. I inherited this database from a guy who started out with no idea what he was doing. Although I've been fixing snippets here and there, I still run into bits that make me wonder what he was thinking.

[NeoPa]

Let me know how you get on ADezii.

If you need me to I'll have a look at it. Experts do deserve a little extra effort I feel.

[ADezii]

Downloaded thye DB, will look at it tomorrow.

[ADezii]

Hello topher23, just wanted to let you know that I started looking through the Database, and so far have not found anything that would cause this strange anomaly. As you know, I am not a stranger to this weird behavior, since I experienced this sort of thing before, only in reverse (text disappearing with MouseOver). Unfortunately, I never resolved my problem but hopefully we can resolve yours. Maybe we can get NeoPa in on the party, the more eyes the better.

BTW - Save yourself the trouble of Importing all Objects into a New Database, since it will not eliminate this pesky creature! Neither will relocating the affected Controls, elimination of all MouseMove() Events, relocation of IIf() Construct to the Current() Event, recreation of the Controls, etc...

[ADezii]

@NeoPa
Probably could use your help on this one, Olde Buddy!

[topher23]

@ADezii
Haha! Yeah, I've tried most of those to no avail. It does, however, make me perversely happy to knwo that I'm not the only one who has spent far too much time banging his head against a wall trying to figure out this kind of problem!

[ADezii]

It at first would appear that you have Controls (Text Boxes) in the Header of the Main Form whose Control Sources reference Controls (Text Boxes) in the Form Footer Section of the Sub-Form whose Control Sources themselves are not Fields but Aggregates of Sum() and Count() Functions. It's sort of similar to an Expression referencing another Expression. You did say that this used to work, correct?

[NeoPa]

@ADezii
I'll download it & have a look over the weekend. I can't promise I'll find anything, and I have no experience with similar stuff as you do ADezii, but I can look anyway and see what I see.

[ADezii]

Thanks NeoPa, probably something we overlooked.

[ADezii]

After many hours, I do believe that I have found the solution, and it is:
For the On Mouse Move Event Property for the Detail Section of the 'Main Form' (frmEntry_Main), you have the following reference to a Public Function in basUtilities, namely:

=slib_ResetButtonBitmap("frmEntry")

DELETE THIS LINE and you should be sleeping better at nights (LOL).

P.S. - frmEntry does not exist, and for some Unknown reason, appears to be causing this Anomaly.

[NeoPa]

Well, I finally got back on this afternoon (The local power sub-station was being upgraded and we lost power for two hours while they routed a massive generator through to our power lines - 1,250 KVA I believe.), only to find that ADezii, like a dog with a bone, managed to get to the heart of the issue before I even needed to download the file. Excellent :)

[ADezii]

Only when I get the confirmation from topher23 that this issue has been resolved, will I consider it solved (rhyming not intentional)! (LOL).

[NeoPa]

That's fine :)

I possibly have more confidence in you :) so I'll wait to be told otherwise before I jump in. I'm happy to if required, but this can be quite labour-intensive, and I never liked hard work :D

[ADezii]

but this can be quite labour-intensive, and I never liked hard work :D

I can't believe that administering this Forum is easy (LOL).

[topher23]

Sorry I haven't gotten back sooner. I deleted the sub call and got no change. Still trying to figure this mess out. :(

[NeoPa]

I tried it and it reported a broken Reference link of :
C:\DATABASES\Shared\gif89.dll

[topher23]

Whoops! I removed the animated GIF controls for the upload, but it appears I forgot to remove the reference. That *shouldn't* have any bearing on the issue at hand.

[topher23]

Well, I've been able to figure out something, at least. The reason that mousing over txtShiftDiff makes all of the referencing text boxes suddenly "visible" is related to the conditional formatting on txtShiftDiff. When I took off the conditional formatting, txtShiftDiff suddenly lost its magical abilities. Unfortunately, this does nothing to solve the problem in the first place.

All the text boxes in the header reference calculated expressions on the subform, but there are other text boxes in the footer that reference bound fields on the subform. None of them had been working correctly. I removed the control source from the header text boxes, making them unbound, and had a look at the form. Suddenly the boxes in the footer were working. Ergo, the problem must be with referencing the expressions in the subform. The oddest thing is that this issue is unique to this form - I have other forms in other databases doing the same thing (referencing a calculated field on a subform) with no problem.

Since no amount of tweaking seems to be fixing the problem, I think my solution to this issue is going to end up being kludgy: making each of these text boxes Unbound, moving everything out to a public subroutine and using DSum/DCount statements on tblData2 to populate the fields that currently reference expressions in the subform. It may actually end up being better, since I will be able to update the data in each text box without having to requery it individually when something in the subform changes.

[ADezii]

Just so you and NeoPa do not think that I am out of my mind, I'm sending a fully functional Version of the DB to your private E-Mail Address. The only differences are:

1. I removed the Reference to the gif*.dll.
2. I modified the Reference to the Outlook Library to 10 to conform to my PC.
3. Opened the Main Form to the First, as opposed to a New Record.
4. Deleted the offending Line of Code as indicated in Post #12.
5. Kindly get back to me on this one, since the guys with the sterile white coats are now knocking on my door! (LOL).

[ADezii]

I restored the Reference to Outlook 11 on another PC, but the Reference to the Gif*.dll was removed. See Attachment, and get back to me on this, please.

[topher23]

Thanks - I think my company's filter caught the emailed version...

[NeoPa]

I've looked at that and not seen the error described. I wasn't clear where I should be looking, as the explanation refers only to "several unbound textboxes that reference fields in a subform.", however I did move the mouse all over and never saw anything approaching the behaviour described.

I did get a strange error message when I clicked Exit though :

Error #3078 in CloseClearBlanks - The Microsoft Jet database engine cannot find the input table or query 'Table1'. Blah, blah, blah.

When I clicked OK it promptly dumped me right out of Access.

None of my references was flagged as in error, yet it indicates I have a Microsoft Office 12.0 Object Library, which is a little strange as I've never installed anything 2007 on my PC (except maybe a compatibility pack for Word & Excel documents - Maybe that's it).

[topher23]

Holy carp, Adezii, you've got the solution, but it's not what you thought!

When I opened the attachment, I noticed immediately that:

a) it worked!
b) there was a bunch of unused space at the bottom.

So, I went into design view and saw that the detail section had been expanded from where I had it (nothing) to about a screen inch. So, I closed it down to nothing and went back to form view. Suddenly, those calculated fields don't work any more. Intrigued, I try expanding the detail section and, voila, everything works again. So, I pull up a develepment copy of the full database, expand the detail section one grid unit, save the change and open it in form view. Wouldn't you know, it works!

How's that for the wierdest, most random, unrelated issue and solution ever?

[topher23]

Sorry about that error, NeoPa, these forms do quite a bit of background work on several different tables as the user goes along. That particular button runs a check on a table called (sadly) Table1 for erroneous records before quitting Access.

Table1 wasn't included in the piece that I uploaded because it's no longer used directly by the form.

[NeoPa]

@topher23
Seriously underrated fish I always say :D

Nice spot by you too Topher. Congratulations to both of you.

[NeoPa]

@topher23
No worries. Sometimes it's difficult to get a completely consistent, yet cut-down, version of a database to test. You tried at least, which is definitely a good thing :)

[ADezii]

How in the world does an expanded vs non-expanded Detail Section affect this outcome? It is now my life's mission to find out how...

[topher23]

If you want to pursue this, Adezii, feel free to forward that database snippet on to any of those Access team folks that hang out on UtterAccess. Maybe they'll have an idea of what the dependency is that's causing this problem.

[NeoPa]

@ADezii
That's not good :(

Topher, I hope you realise you just took out one of our best experts with a DoS attack!

[ADezii]

Haven't been on that site for a long time, and have no intention of ever returning. My home is here, and here is where I shall remain! (LOL)

[NeoPa]

@ADezii
I just want to clarify that my earlier post was refering to your focus being on digging out the confusing problem Topher left you with. Not that we have any objection to any of our experts having lives outside of Bytes.

We don't appreciate links across, but that's another story and well-covered in the rules.

[ADezii]

Here is another mind blower, topher23. Closing the Detail Section of the Main Form
(Height = 0), and running the following Line of Code in the Main Form's Open() Event will produce the desired results.

Me.Section(acDetail).Height = 8

The listed Code will programmatically set the Detail section's Height in the Main Form to 8 Twips, or .0056 (56 Ten Thousandths) of an Inch. Here is the really weird part, setting the Detail Height to anything lower than 8 Twips (>=1 and <8), the problem will return again.

P.S. - All out of revelations for today, I'll see what I can come up with tomorrow.

[NeoPa]

I don't often assign the OP's answer as Best Answer, but in this case, even though ADezii did most of the work, I felt Topher's post most clearly explained the issue for those following on, and I believe there will be a few on this one. An interesting thread.

[ADezii]

I felt Topher's post most clearly explained the issue for those following on...

Couldn't agree with you more, NeoPa, topher23 definately deserves the Best Answer. Personally, I would like to see this issue completely resolved. It already bit me twice in the $%@! (LOL)

[topher23]

@ADezii
I whole-heartedly agree (which is why I live here rather than there). I was just saying that I have no objection to you forwarding the database snippet to one of the folks who actually develop Access for Microsoft to see if they can see where they screwed up. ;)

[topher23]

@ADezii
Aww, I'm touched. And in a good way, not in a "let's play a game" kind of way.