How to capture multiple values using one combobox?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

MS Access 2K, Windows XP
====================
Greetings!

I have a combo-box with the row source a query with 2 different IDs and
name. The first 2 columns (IDs) are hidden, and only the name column is
visible. So, the query looks like this:

ID_1 ID_2 Name
==== ==== ====
1 2 A
2 2 B
3 1 C
4 3 D
5 2 E

I need to capture and store both the IDs (ID_1 and ID_2) related to the
selected name. Is there a way to do this without programming? As far as I
know, only one column can be bound. Should I use DLookUp on the query using
the bound column (ID_1) after the combo-box is updated to get the second ID?
By the way, ID_1 is the Primary Key here.

Thanks for any pointers, and have a wonderful weekend.

-Amit
 
MS Access 2K, Windows XP
====================
Greetings!

I have a combo-box with the row source a query with 2 different IDs and
name. The first 2 columns (IDs) are hidden, and only the name column is
visible. So, the query looks like this:

ID_1 ID_2 Name
==== ==== ====
1 2 A
2 2 B
3 1 C
4 3 D
5 2 E

I need to capture and store both the IDs (ID_1 and ID_2) related to the
selected name. Is there a way to do this without programming? As far as I
know, only one column can be bound. Should I use DLookUp on the query using
the bound column (ID_1) after the combo-box is updated to get the second ID?
By the way, ID_1 is the Primary Key here.
Click to expand...

You do need programming, but not much:

Private Sub comboboxname_AfterUpdate()
Me!txtID_2 = comboboxname.Column(1)
End Sub

The Column() property is zero based so (1) means the second column.

One question: if ID_2 can always be determined given the value of
ID_1, why store it in this form's table at all? It's redundant.

John W. Vinson[MVP]
 
The bound column should be set to 1 which will make the combobox have the
value of ID_1. Add a textbox named ID_2 to the form and bind it to ID_2. Put
the following code in the AfterUpdate event of the combobox:
Me!ID_2 = Me!NameOfCombobox.Column(1)

When you save the record on the screen, both ID_1 and ID_2 will be saved to
your table.
 
John Vinson said:
You do need programming, but not much:

Private Sub comboboxname_AfterUpdate()
Me!txtID_2 = comboboxname.Column(1)
End Sub

The Column() property is zero based so (1) means the second column.

One question: if ID_2 can always be determined given the value of
ID_1, why store it in this form's table at all? It's redundant.

John W. Vinson[MVP]
Click to expand...

Hi John,

Thanks for the quick response. Short answer would be - it's 6pm on Friday,
and I'm brain-dead :)

Actually, my question was a sub-set of a bigger issue. The source of the
combo-box is a Union query, and I need to capture two sets of IDs, both of
which combined are PK, but individually, are not. So, I was approaching the
solution to this problem the longer way, by having another column of unique
ID (ID_1 in my question) act as the PK, and then capturing the 2 sets of IDs
that I actually need using this unique ID. However, as your reply elucidates,
I don't need ID_1 at all, and can capture the other two sets of IDs (in my
question, I simplified by having only ID_2 instead of ID_2A and ID_2B)
directly using the code in AfterUpdate.

Hope this makes sense, and thanks again for simplifying the solution :)
Have a wonderful weekend!

-Amit
 
Back
Top