How to capture multiple values using one combobox?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Amit - 21 Jan 2005 23:01 GMT

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

Reply to this Message

John Vinson - 21 Jan 2005 23:41 GMT

>MS Access 2K, Windows XP
>====================
[quoted text clipped - 17 lines]
>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.

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]

Reply to this Message

Amit - 22 Jan 2005 00:03 GMT

> >MS Access 2K, Windows XP
> >====================
[quoted text clipped - 30 lines]
>
> John W. Vinson[MVP]

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

Reply to this Message

PC Datasheet - 21 Jan 2005 23:44 GMT

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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

> MS Access 2K, Windows XP
> ====================
[quoted text clipped - 21 lines]
>
> -Amit

Reply to this Message

Amit - 22 Jan 2005 00:05 GMT

Thanks for the prompt response, and the answer I was looking for.

-Amit

> 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
[quoted text clipped - 37 lines]
> >
> > -Amit

Reply to this Message