Selecting a value from a drop-down box

[Joseph Greenberg]

I have a drop-down box that is defined as two columns, but the first column
is 0 width, so it's not visible (intentionally). The drop-down is bound to a
data field but is populated by a query from a lookup table, and while the
dropdown displays the text from the second column, the database stores the
numbers from the first (invisible) column. I have an outside function that
returns a string that corresponds to the text values shown in the drop-down
(but wouldn't get stored in the database). Does anyone know how I can
essentially lookup the values from the dropdown based on the text string,
and then pass the corresponding number value to the database?

For example, my lookup table and drop down have these values:

1 Red
2 Blue
3 Green
4 Violet
5 Brown
6 Black

My outside function returns a global variable that contains "Violet". How
can I get a "4" to my database? And I can't really change the outside
function.

 

[Al Campagna]

Joseph,
The first column's value (the number) is what is being stored in the
field that is "bound" to your combobox.
The setup you described is commonly used to capture
a unique key value... like a CustID, or PartNo...
NoOfCols = 2
ColWidths = 0"; .5"
Bound Col = 1
It allows the user to make a selection by a meaningful value, like
color name, or part decription. The combo "displays" that name value,
but really captures the first columns data into the field it's bound to.

Tip: The Bound property looks at combo columns as
1, 2, 3, 4, etc...
When referring to combo column values, they are numbered
0, 1, 2, 3, etc...
Try this... place a text control on the form, with a control source
of...
= YourComboName.Column(1)
and see what value shows after a selection is made. You should see
the key number value for the color being displayed.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Joseph Greenberg]

thanks, i got this figured out now.

Joseph,
The first column's value (the number) is what is being stored in the
field that is "bound" to your combobox.
The setup you described is commonly used to capture
a unique key value... like a CustID, or PartNo...
NoOfCols = 2
ColWidths = 0"; .5"
Bound Col = 1
It allows the user to make a selection by a meaningful value, like
color name, or part decription. The combo "displays" that name value,
but really captures the first columns data into the field it's bound to.

Tip: The Bound property looks at combo columns as
1, 2, 3, 4, etc...
When referring to combo column values, they are numbered
0, 1, 2, 3, etc...
Try this... place a text control on the form, with a control source
of...
= YourComboName.Column(1)
and see what value shows after a selection is made. You should see
the key number value for the color being displayed.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Sam Davis]

Hi Joseph,

As the drop down is bound to a field I think you'll need to search the
column containing the text for a match to the text returned from your
outside function.

If the drop down is called cboColours, something like....

Me.cboColours = Null
For r = 0 To Me.cboColours.ListCount - 1
If Me.cboColocurs.Column(1, r) = YourOutsideFunction() Then
Me.cboColours = Me.cboColours.Column(0, r)
End If
Next

HTH
Sam