How to assign a value to an unbound text box in datasheet view on subform

[John Hackert]

Basic issue:
A combobox on a subform has 2 columns
Column 2 is a number (an ICD code); column 1 is a description of the
code

Because it's more meaningful to search on the description than the code
(eg, "abdominal pain"), I wished to make column 2 the bound column.
After a selection, the description remains in the text box portion of
the combo, even though the code itself is stored in the underlying
table. In form view, it's simple to add an unbound text box to display
the bound code.

In a multi-line datasheet view, however, the column of unbound text
boxes displays the value of the most recently selected combo box item
on each row, no matter which technique I've tried to "re-assign" it to
the respective row's combo box value.

Any way to overcome this? It would seem easy enough to simply bind the
value (again) to the table, but not the best design.

Thanks

 

[Damian S]

Hi John,

Have you considered using a continuous form instead of datasheet view. It
gives you so much more control over the formatting.

Damian.

 

[Marshall Barton]

Basic issue:
A combobox on a subform has 2 columns
Column 2 is a number (an ICD code); column 1 is a description of the
code

Because it's more meaningful to search on the description than the code
(eg, "abdominal pain"), I wished to make column 2 the bound column.
After a selection, the description remains in the text box portion of
the combo, even though the code itself is stored in the underlying
table. In form view, it's simple to add an unbound text box to display
the bound code.

In a multi-line datasheet view, however, the column of unbound text
boxes displays the value of the most recently selected combo box item
on each row, no matter which technique I've tried to "re-assign" it to
the respective row's combo box value.

Any way to overcome this? It would seem easy enough to simply bind the
value (again) to the table, but not the best design.

Since the text box is unbound there is nothing about it that
makes it different from one row to another.

Off the top of my head you can easily bind the text box to
the same field as the combo box. You should probably set
the text box's Locked property to Yes and the Enabled
property to No to prevent users that might be inclined to
edit the text box value.

 

[AccessVandal via AccessMonster.com]

Hi John,

You might want to try the something from Pat Hartman then, look for Ken Snell
site.
http://www.cadellsoftware.org/SampleDBs.htm

 

[Steve Schapel]

John,

... bind the
value (again) to the table, but not the best design.

Why not?

 

[John Hackert]

Thanks for all the responses.

Marsh and AccessVandal, I looked at the sham text box solution, so
nicely demonstrated in "Ken's Sample Databases." Perhaps a little too
die-hard of a workaround for me.

Damian, I also tried the Continuous Form approach, a format I have not
happened to experiment with before. Although I can see the whole new
world of formatting possibilities over a simple datasheet, unless I
still haven't set the code up right, a given record's unbound text box
does not retain its value when another record is updated.

As Steve Schapel suggested, perhaps it's best to just bind the
associated data in the text box afterall. The reason not to would of
course be the same argument against binding a calculated value. But
here, it's a minor design-price-to-pay to store both the ID (which
happens to be meaningful, unlike the usual autonumber) and its
descriptor. This is further valid because the relationship between an
ICD and what it encodes may change over time (ie, dropped, added, or
modified).

John

 

[Steve Schapel]

John,

Yes, I agree that the information on Ken Snell's website (while
excellent for its intended purpose), is not applicable to your situation
or the problem you described.

And yes, you are right, the value in an unbound textbox on a continuous
form suffers from the smae problem.

But after that, I'm afraid you lost me. I did not realise you were
proposing to store the Description in the form's underlying table. I
thought that the combobox was bound to the ICD Code field - but
*displaying* the Description. All I was suggesting was binding the
textbox to the ICD Code as well. This is very simple, and a perfectly
acceptable thing to do, for which I can think of no valid objection.

Having said that, I would not do it that way myself... I only want
along with what I thought was you r suggestion, and agreed that it is a
good solution. For myself, I would do it like this:
- set up the combobox so that the ICD Code is the first column and the
Description is the second column, and set it up so you can see the data
in both columns when you drop down the combobox list.
- leave it so that the ICD Code is the bound column of the combobox.
- set the textbox's Control Source property to the equivalent of...
=[NameOfCombobox].[Column](1)

That way you can see the Description when selecting from the combobox,
but after the selection is made, the ICD will be displayed in the
combobox and the Description in the textbox.

The only disadvantage of this approach would be if you wanted to use the
combobox's Auto Expand related to the Description column. If that would
be one of your requirements, we would need to re-think.

 

[John Hackert]

Steve,

Thanks for your interest with this problem.

What I was planning to go to as a compromise was to bind the ICD code
from the previously unbound text box, and change the bound column of
the combo to the ICD descriptor. At least two distinct (albeit
essentially directly related) pieces of data would be stored. It would
seem absurd to open the table and see two columns bearing the exact
same information if the ICD code were stored twice side by side.

My original problem revolved around the wish to search on the text of
the descriptor, not the list of ICD codes. In the case where the ICD
code itself is known, it could be entered into the text box, from which
the combo could be updated programmatically.

As I dwelled on this a little more, I turned to my trusty copy of Getz,
Litwin, and Gilbert's Developer's Handbook and more or less stumbled on
this sensible workaround, which they entitled "Present a Calculated
Value, List Individual Fields":

From their example, I placed an additional column in the combo box as

the first column which is a query-provided concatenation of the ICD
descriptor and the ICD code, setting the width to near zero such that
it is unable to be displayed in the drop down view. In drop down, the
previously first column, the descriptor, effectively becomes the
searchable first column. After a selection, however, the true first
column is displayed in the text portion of the combo, in the format of
"Abdominal pain 789.0." I left the ICD text box on the form as a way
to be able to enter "789.0," e.g., if I knew that code off hand, again
updating the combo programmatically, but clearing the text box
afterward. The bound field of the combo remains the ICD code, as
desired. And of course in the datasheet view of the subform, the
descriptor and code are properly displayed on each line at long last.

John,

Yes, I agree that the information on Ken Snell's website (while
excellent for its intended purpose), is not applicable to your situation
or the problem you described.

And yes, you are right, the value in an unbound textbox on a continuous
form suffers from the smae problem.

But after that, I'm afraid you lost me. I did not realise you were
proposing to store the Description in the form's underlying table. I
thought that the combobox was bound to the ICD Code field - but
*displaying* the Description. All I was suggesting was binding the
textbox to the ICD Code as well. This is very simple, and a perfectly
acceptable thing to do, for which I can think of no valid objection.

Having said that, I would not do it that way myself... I only want
along with what I thought was you r suggestion, and agreed that it is a
good solution. For myself, I would do it like this:
- set up the combobox so that the ICD Code is the first column and the
Description is the second column, and set it up so you can see the data
in both columns when you drop down the combobox list.
- leave it so that the ICD Code is the bound column of the combobox.
- set the textbox's Control Source property to the equivalent of...
=[NameOfCombobox].[Column](1)

That way you can see the Description when selecting from the combobox,
but after the selection is made, the ICD will be displayed in the
combobox and the Description in the textbox.

The only disadvantage of this approach would be if you wanted to use the
combobox's Auto Expand related to the Description column. If that would
be one of your requirements, we would need to re-think.

--
Steve Schapel, Microsoft Access MVP

Thanks for all the responses.

Marsh and AccessVandal, I looked at the sham text box solution, so
nicely demonstrated in "Ken's Sample Databases." Perhaps a little too
die-hard of a workaround for me.

Damian, I also tried the Continuous Form approach, a format I have not
happened to experiment with before. Although I can see the whole new
world of formatting possibilities over a simple datasheet, unless I
still haven't set the code up right, a given record's unbound text box
does not retain its value when another record is updated.

As Steve Schapel suggested, perhaps it's best to just bind the
associated data in the text box afterall. The reason not to would of
course be the same argument against binding a calculated value. But
here, it's a minor design-price-to-pay to store both the ID (which
happens to be meaningful, unlike the usual autonumber) and its
descriptor. This is further valid because the relationship between an
ICD and what it encodes may change over time (ie, dropped, added, or
modified).

 

[Steve Schapel]

Very good, John. I am very happy to hear that you have arrived at a
solution that suits you. I considered that approach, but decided not to
mention it because it seems unnecessarily complicated and potentially
confusing. But if you like it, that's fine. I would still prefer
either of the solutions that I suggested.

The only further thing I would point out is that you can have more than
one control on a form bound to the same control. In other words, having
2 controls on the form bound to the ICD Code field would not imply 2 ICD
Code fields in the table.

 

[John Hackert]

Steve,

Ockham's Razor to the rescue. I had not considered that it is possible
to assign more than one control to the same control source.

So, even though the "Present a Calculated Value, List Individual
Fields" trick was fun to learn, in this case clearly the simplest and
most effective technique is to modify the original setup only by
changing the text box from unbound to bound on the underlying table's
ICD code. I tested this and it works perfectly: I can search on an
ICD by its description in the combo bound to the ICD, which I
programmatically place in the text box after selection; conversely, I
can enter an ICD code directly in the text box and programmatically
update the combo to show its description. In the compact datasheet
view on the subform as desired, the combo and text box are
"synchronized" line-by-line, eliminating my original problem.

Thanks again for your follow up on this issue. If Microsoft were to
monitor your contributions as an MVP, I would hope they issue "extra
credit" to you for your discernment on this problem.

 

[Steve Schapel]

John,

Your confidence in my powers of discernment have encouraged me to
further comment ...

What are "programmatically place the ICD in the text box", and
"programmatically update the combo to show the description" all about?
There should be no need to programmatically do anything at all. If the
combobox and the textbox both have the ICD Code field as their Control
Source, then any "updating" of the values shown in these controls should
occur automatically.

By the way, thanks for pointing to Ockham's Razor. It was a long time
ago that I last saw mention of this, so it is good to be reminded of it,
as it is a priciple which in practice I often try to apply.

 

[John Hackert]

Steve,

You're so right...No need for something like "Me.txtICD =
Me.cboICD.column(1)" after a selection is made in the combo, or vice
versa.

I'm amazed at how effortlessly the form populates the opposing control
sharing the same control source, without even having to requery the
control.

Thanks again for your help - an elegant solution that I would not
likely have figured out on my own.

John

 

[Steve Schapel]

... an elegant solution that I would not
likely have figured out on my own.

.... the most surprising aspect of which, is that this is precisely what
I imagined you were referring to in the final sentence of your original
post, and all I was wondering was why you didn't think it was a good
idea. Funny.

Anyway, Ockham strikes again!

 

[Marshall Barton]

... the most surprising aspect of which, is that this is precisely what
I imagined you were referring to in the final sentence of your original
post, and all I was wondering was why you didn't think it was a good
idea. Funny.

Yeah, that's what I thought too. Nice to know I was in good
company ;-)