I spent all day working on this yesterday and I think I've got a pretty good solution. So I wanted to share my findings with
others in case it might help anyone else. I was also hoping that people might offer suggestions on how to improve on what
I've got or ideas of other possible solutions.
Here's the problem: I've got a form with 4 unbound "drill down" combos, each of which changes its row source based on the
value of the previous combo, that I'm trying to convert to continuous form view. To make it more complicated, combos only
become visible after a selection is made in the previous combo and the depth of the drill down varies per record (i.e., some
combos won't ever be made visible on some records).
Here's my solution, thus far:
1) Create a text box to cover each combo and give it an On Enter event that sets the focus to the combo behind it. (These
text boxes will be referred to as "display text boxes" from now on.) I first found an example of how to do this on Allen
Browne's web site, but it seems that this solution is well-documented in many other places as well. (Allen Browne's example
doesn't actually deal with combos, but it was easy to adapt it to my needs.)
2) Fill the display text boxes with values based on bound data. In my case, all four unbound combos serve to drill down to
AccountID, so the values of the text boxes can be generated based on the current record's AccountID. I used a (hidden) combo
box (cboAccountID) with several columns to accomplish this. For example, the control source for the first display text box is
3) Create Form_Current event to set all of the unbound combos to match the values in the display text boxes in front of
them. In this case, I also took this info from the columns in cboAccountID. In Allen Browne's example, he sets the value of
the unbound control only when he needs to access that control, but I'm doing it in the On Current event because I can't set
one combo without also setting all the combos before it. Anyway, that's the way that made more sense to me at the time.
4) Create text boxes to cover each combo box that you want to hide. (In future I will refer to these text boxes as "hide
control" text boxes.) Set background transparent and set font color to match the form background. Set font to Terminal and
font size to 127 (as large as possible). Set control source to
Substitute for chkHide any check box or True/False expression based on bound data. ("Û" in Terminal is a black box
(better than other black box characters I tried because it didn't leave any blank space between characters, at least not on
my system).) Thanks to Kevin Gray's ColorCon example for the "=IIf(...,'ÛÛ')" stuff and to Pavlo Pedan's "Conditional
Formatting (Visibility)" example for the idea of how to hide controls on continuous forms.
That seems to get us most of the way there. The main obstacle left to tackle is that the current record should display
differently than the others. As it stands, the combos are always covered by the text boxes in front of them. So if I enter
something in combo1 and then jump to combo2, combo1 immediately becomes covered up by its text box and I can't see the value
that I just entered. So we need to set it so that the appropriate unbound combos are always visible for the current
Formatting current record:
5) Create a hidden unbound text box to store the primary key of the current record. Add code to the form's On Current event
to update the value of this text box. In my case, the code looks like this:
Me.txtCurrentRecord = Nz(Me!SpltID, 0)
I also had to put this code in a couple other places in my module. For example, when you're entering a new record the
Form_Current event will set txtCurrentRecord to 0, so you need to update it when your record is given a new primary key
value. Now you can format the current record using the following expression:
(Use your primary key in place of SpltID.) Thanks to user pere_de_chipstick at utteraccess.com for this solution.
6) Set the background of the display text box to transparent and create another text box of the same size in the same
location. Set the formatting for this text box the same as that of the "hide control" text box. Set the control source to the
(Again, substitute primary key.) Position this text box behind the display text box but in front of the combo box. Now
all records will display the same as before except for the current record, which will always show the unbound combos (except
where the "hide control" text boxes are filled).
7) Make sure that your display text boxes are always either empty or the same as your combos for the current record,
otherwise you'll see the text of both controls on top of each other. I guess this is another reason that I update all the
combos to the values of the display text boxes in Form_Current. The other thing that I do is clear or update the value of the
display text boxes when I change the value in any of the unbound combos.
8) (Optional) Create transparent command buttons on top of the "hide control" text boxes with On Click events that set focus
to the combo boxes (if the combo boxes are visible). I did this because I didn't like that when the "hide controls" text
boxes were filled (i.e., you couldn't see the combo behind them) the cursor would still change from an arrow to a text
cursor, even though the user didn't see any text field to edit. Now it stays an arrow for those fields. (This is still not
perfect because it should turn to a text cursor when the combo is visible, but I didn't know how to fix that.)
OK. I think that's it. I'm pretty sure I didn't leave out any important steps. See attached example database for more info.
The example database was pulled from an actual database I'm working on, so it's possible that it will have broken references,
but I'm pretty sure it's good. As you can see, the form that I created is for entering split transactions in a bookkeeping
database. The old version of this form didn't use continuous forms and was therefore much simpler to design, but the users
found it confusing so I'm going to try this to see if it makes more sense to them.
So... is this useful to anyone else? Is the example too specific? Should I have broken it into separate threads?
More importantly (to me), does anyone have any suggestions on how to do this better? Or how to minimize flicker? Or how to
design a better form for entering split transactions?
I wanted to link to the examples I cited, but I'm too much of a newbie to be allowed to include links.