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:
Basics
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 set to
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.
Hiding combos
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 record.
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:
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 following:
(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.
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:
Basics
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 set to
Code:
=[cboAccountID].[Column](1)Hiding combos
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
Code:
=IIf([chkHide],"ÛÛÛÛÛ","")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 record.
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:
Code:
Me.txtCurrentRecord = Nz(Me!SpltID, 0)
Code:
[txtCurrentRecord]=Nz([SpltID])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 following:
Code:
=IIf([txtCurrentRecord]<>Nz([SpltID]),"ÛÛÛÛÛ","")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.
