Allow user to keystroke the value, but store the label

[Guest]

I've never found a decent solution to this, but it seems it should be easy.

I've created a data entry form to enter survey responses. Every answer has a
value associated with it. For example, the field Sex:

Value Label
--------------
1 Male
2 Female
99 Unknown or Missing

The control on the form is a combo box which uses a query to look up the
values in tblLookup. The combo box displays 2 columns, the value and label.

On the data entry form, I would like the user to be able to keystroke the
value (e.g., hit "2" for Female), but have the *label* display in the box and
have the label stored in the table.

My bound column is set to 2 (the label), but when the user presses a "2" for
Female, the "2" is displayed in the box on the form. How can I have the label
displayed?

Thanks.

Kurt

 

[Dirk Goldgar]

I've never found a decent solution to this, but it seems it should be
easy.

I've created a data entry form to enter survey responses. Every
answer has a value associated with it. For example, the field Sex:

Value Label
--------------
1 Male
2 Female
99 Unknown or Missing

The control on the form is a combo box which uses a query to look up
the values in tblLookup. The combo box displays 2 columns, the value
and label.

On the data entry form, I would like the user to be able to keystroke
the value (e.g., hit "2" for Female), but have the *label* display in
the box and have the label stored in the table.

My bound column is set to 2 (the label), but when the user presses a
"2" for Female, the "2" is displayed in the box on the form. How can
I have the label displayed?

How is the user supposed to know that they can enter 1 to get "Male", 2
to get "Female", and so on?

I'm not sure this is good UI design, but you could use an AfterUpdate
event procedure to transform numeric entries to the corresponding
"label" values, like this:

'----- start of code -----
Private Sub cboSex_AfterUpdate()

Dim I As Long

With Me.cboSex

If IsNumeric(.Value) Then

For I = 0 To (.ListCount - 1)
If .Column(0, I) = .Value Then
.Value = .ItemData(I)
Exit For
End If
Next I

End If

End With

End Sub

'----- end of code -----

That won't keep the user from entering a numeric value that isn't in the
list, though. For that you'd need extra code, maybe in the control's
Exit event.

 

[Dirk Goldgar]

How is the user supposed to know that they can enter 1 to get "Male",
2 to get "Female", and so on?

I'm not sure this is good UI design, but you could use an AfterUpdate
event procedure to transform numeric entries to the corresponding
"label" values, like this:

'----- start of code -----
Private Sub cboSex_AfterUpdate()

Dim I As Long

With Me.cboSex

If IsNumeric(.Value) Then

For I = 0 To (.ListCount - 1)
If .Column(0, I) = .Value Then
.Value = .ItemData(I)
Exit For
End If
Next I

End If

End With

End Sub

'----- end of code -----

That won't keep the user from entering a numeric value that isn't in
the list, though. For that you'd need extra code, maybe in the
control's Exit event.

I suppose you could add something like this:

'----- start of code -----
Private Sub cboSex_Exit(Cancel As Integer)

With Me.cboSex
If .ListIndex < 0 And Not IsNull(.Value) Then
MsgBox "That's not a valid value!"
Cancel = True
End If
End With

End Sub
'----- end of code -----

 

[Albert D.Kallal]

Simple answer:

just un-hide the first column!!! The user will now see both columns..and
searching will occur by the first column (number).

So, if they know the number..they just type it in...and if they don't...then
they hit the drop down arrow....

Since now the combo box will show the number when it is collapsed..then you
need a text box right after the combo box (without a tab stop to save the
user having to tab past) that will display the actual label text
(description text).

just put the 2nd column text as the control source of this text box

=([mycombobox].column(1))


So, you don't need any code...and the changes to the combo box will be very
small....

 

[Guest]

How is the user supposed to know that they can enter 1 to get "Male", 2

to get "Female", and so on?

The combo box shows both columns, the value (width = .3") and the label
(width = 1"). So the user sees:

1 Male
2 Female
99 Unknown Missing

The bound column is 2, so when the user selects "1," "Male" is correctly
stored in the table. However, "1" is *displayed* in the form's control, just
like it's *displayed* in the table. I would like "Male" to be displayed in
the form's control once it's been selected. (I don't care how it's displayed
in the table.)

 

[Guest]

just un-hide the first column!!! The user will now see both columns..and

searching will occur by the first column (number).

So, if they know the number..they just type it in...and if they don't...then
they hit the drop down arrow....

The first column is already unhidden. It's the second issue I'm trying to
resolve ...

Since now the combo box will show the number when it is collapsed..then you
need a text box right after the combo box (without a tab stop to save the
user having to tab past) that will display the actual label text
(description text).

just put the 2nd column text as the control source of this text box

=([mycombobox].column(1))

This works great, but I will need to do this for many more items. (It's a
data entry form for a survey, so I've got around 50 items.) It would be nice
if Access added a property where you could pick which column to display in
cases like this. Like you said, the combo box only shows the number when
collapsed.

By the way, just curious: Your control source works perfect, even though my
Label column is the second one. So why does the control source refer to
column 1? I would think this would make it display the first column, which in
my case is the Value.

Thanks.

Kurt

 

[tina]

it would be more intuitive for the user to type "m" and get "Male", or type
"f" and get Female, or type "u" and get "Unknown or Missing". you can
accomplish this by changing the ColumnWidth property of the combo box to

0"; 1"

you don't have to make the second column 1 inch, make it as wide as you
wish. the important point is that the first column is set to zero width, so
the 1, 2, 99 values don't show in the droplist OR in the control after a
selection is made.

hth

 

[Albert D.Kallal]

So why does the control source refer to
column 1?

It is zero based...as many collections an properties in vb/ms-access are.

So column (0) is the first column.....1 is the 2nd...etc....

So, column(1) actually means to grab the 2nd column...

 

[Dirk Goldgar]

The combo box shows both columns, the value (width = .3") and the
label (width = 1"). So the user sees:

1 Male
2 Female
99 Unknown Missing

The bound column is 2, so when the user selects "1," "Male" is
correctly stored in the table. However, "1" is *displayed* in the
form's control, just like it's *displayed* in the table. I would like
"Male" to be displayed in the form's control once it's been selected.
(I don't care how it's displayed in the table.)

Yes, you can get it to display "Male" and still allow the user to enter
"1", but only if LimitToList is set to No and using translation code
such as I posted in the AfterUpdate event. My point, though, was that
the user will *not* see both columns except when the combo's list is
dropped down, so the user won't know that the number 1 is associated
with the value "Male", and won't think to enter it unless instructed to
do so.