Access being helpful again. LOL

This TechHelp made me both happy and sad in a highly nerdy sense...

About a yr and a half ago I was asked to do some simple clean-up of forms for a database already built. The main issue was that there was 1 manager at "client" who was trying to insist that Access was dead and couldn't look/work as good as modern systems and was trying to convince owner to discard it, spend a fortune on some high end solution to make the company look more sophisticated because "client" was hoping to take the company over in a couple years and thought more expensive software would improve sale price.

Anyway, what made me happy was that the solution I initially proposed to customer was exactly what you did in this video - so good to know I came up with a good solution. Customer was impressed, "client" hated it, saying if it can't show the decimals only in the field on the row being edited while keeping the other rows hidden, that's proof Access is obsolete... Then proceeded to threaten me with legal action for wasting their time and money when they could have gotten a "better" solution sooner.

I told them to hold their horses, I was 2 days ahead of schedule and to let me develop alternatives. So I played around a bit and then sprayed on some Cheese Whiz and brought back two alternatives that kept all other rows without the decimal while showing the decimal in the field being edited, which I didn't like because they were cheesy. Again, owner liked both but even "client" liked them and chose the option I liked least, but I got paid so, at least there was that, but still saddening when a good solution gets tossed for stupidity.

Well, you could use a temporary staging table to show only that row with the decimals. There are all kinds of tricks you can play, but for what we're doing here, I figured the approach in the video works just fine. It doesn't really matter if all of the fields briefly show the decimals while you're editing one of them.

If a client absolutely needed it to work that way, you could load the records into a temporary staging table and use a paginated single form that displays, say, 20 rows at a time. You'd have 20 separate text boxes - Text1, Text2, Text3, and so on - going down the rows. Then you have complete control over the display of each individual textbox.

You could even generate an HTML form in the background and display it with a Web View control. There is almost always a way to make Access do something like this. I've had clients who insisted that a report had to look exactly like some old printed report, and it can be a pain, but anything's possible. It just comes down to how much work you want to put into it and how inflexible the client's requirements are.

Richard
Fascinating. Your demonstration of this technique is most logical.

Actually, I just revisited what I did before if I could peel the cheese off a little and it's actually pretty simple... No VBA actually needed other than I added some for the Limp Bizkit Probability...

Anyway, all I did was put my "fixed" field on top of the field to hide the decimals in... so for example, in you THFT, on the customer list, I would have something like just like it is then a CreditLimitCensor (Display) exactly the same size/formatting as CreditLimit with the control source set to =Fix([CreditLimit]).. Set Enabled to True, Locked to True, TabStop to False, then in the Got Focus event for CreditLimitCensor, just have CreditLimit.SetFocus.

This way when the user tabs into the form, they can't tab into the "fixed" field but they'll tab into the regular, full view, CreditLimit field. If they click on the censored field, it automatically moves them to the Credit Limit field. Then after they're done looking/editing, and tab or click away, Access automatically moves the Credit Limit field back to its original Z-Order position behind the censor field.

I had originally just had part of the sensor field covering the decimal portion which worked for them, but I hated it. Using the full cover version actually doesn't sadden me as much.

Anyway, like I said, I was just happy that the "good" solution I originally figured out ended up being the solution a seasoned developer such as you would use too, lol.

Yeah, I've tried playing around with SetFocus and tab order tricks, along with visible/not visible controls. I run into all kinds of problems with those approaches one way or another. Users always tend to find a way to break stuff like that.

That's why I've settled on just swapping out the Control Source. I figured that was the easiest and most reliable thing to do.

That's why I was saddened with the alternative the "client" chose. But, satisfied to get paid and move on, lol. I did find out that about 8 mos ago, "client" did take over that company, did spend a fortune on new software and rebranding, etc. and did bankrupt the company about 3 mos ago... Such a shame.