dual combo box

  • Thread starter Thread starter Miaplacidus
  • Start date Start date
M

Miaplacidus

I have a combo box that displays two columns. On selecting
an entry one column is stored in the correct database
field.

I would like to store the other column in a different
database field. How would I go about that?
 
First, why do you need to? If the two columns are "joined" you should be
able to access the value of the second column by linking through the first
column. For example, it is normal to do this by having a table that holds a
text value for the user and an autonumber primary key value for Access. When
the user makes a selection in the combo box the value in the number column
is stored in the table. To get the text value for displaying later you would
add both tables to a query and link them on this number field. You could
then retrieve the text value that is associated with the number that has
been stored.
 
The values in neither column are unique, but the
combination of the two values is unique. Therefore I
cannot acess the second column by linking through the
first. The user must have human intelligence and local
knowledge in order to select the right pair.

I suppose I could put the pairs in a table and add a
primary key. Then, as you say I could store the number in
the table and look up both the values when I need to
display them.

However, as time goes by the user may generate new pairs
that are not on the list. Then I need to capture the new
info and put it on the list, which adds another level of
complication.

I can base a combo box on the current values of this
table, have the user select one and enter that value into
the current record, current field. If the user doesn't
select a value he can enter a new one, which will appear
in the combobox next time. Why can't I do that with two
values and two fields? If the pair exists, select it and
store both values in the respective fields of this record;
otherwise enter your own data.

What you say is more "Database Correct", but sometimes it
doesn't make sense. If I go your route, then every time I
look at a table all I see is a bunch of key field numbers.
In order to make sense out of the table I have to go make
a bunch of queries to turn all the links back into a flat
file temporarily, just so I can see the data. Constructing
all the links and keeping track of them may take more time
and space than just storing the (admittedly redundant)
data.
 
In that case, create a textbox on the form (hidden if you wish). In the
AfterUpdate event of the combo box, assign the value of the 2nd column to
the textbox. Bind the textbox to the field you wish to save the value in.
When the record is saved, the value will be saved.
 
That was what prompted my first question: I don't know how
to reference the value of the 2nd column.

BTW the lookup method doesn't work if the values change
over time. Smith is causing the Seattle office to excel in
sales. We open a new office in Takoma and transfer Smith
there. If the Smith/Seattle pair gets changed to
Smith/Takoma, then the Takoma office gets credited with
all of Smiths previous sales.

Dumb example, but you get the idea.
 
To refer to the value in other than the bound column, use the column
property of the combo box.

Example:
Me.txtStoreHere = Me.cboMyCombo.Column(1)

The index number is zero based so 0 is the first column, 1 is the second,
etc.

To handle the other problem, you are also going to need to store the date of
the transaction and the location with each transaction. You can then give
credit where it is due.
 
Back
Top