asaeger said:
Working in a form:
I don't think that is what I'm looking for.
I need to get both columns in the list box to pass to the to two other
columns on a seprate table
Example:
bound column: 2
I want to bind column 2 and 3 to pass to
Control Source: [Used] and [Type]
Thanks..
asaeger......
Dirk Goldgar said:
Asaeger said:
Working in a form:
How can I make a list box selection pass information into two
collums in a table instead of one collum.
Example:
Control Source: Used
Row Source: SELECT [VacType].[ID], [VacType].[Type],
[VacType].[Amount] FROM [VacType] ORDER BY [ID];
A list box can have at most one controlsource. If you *have* to, you
can use the list box's AfterUpdate event to push the value of some
other column into another field on the form. For example,
Private Sub lstUsed_AfterUpdate()
Me!Amount = Me!lstUsed.Column(2)
' Note: that's the *third* column we're using,
' because columns are zero-based in VBA.
End Sub
HOWEVER beware of storing information that is best derived on the
fly by calculation or lookup. It may not be necessary or desirable
to actually store the value of the column. In that case, you can
have a text box with a controlsource that just displays what's in
the list box column for the currently selected row:
=[lstUsed].[Column](2)
You *cannot* bind any control to more than one field. Therefore, if you
*must* store two values from your list box, you can bind the list box to
one of those fields, but then you must use code, like what I posted
above, in the list box's AfterUpdate event, to "push" the other column
value into the other field.
I'm a little confused, though, about exactly which columns you want to
store in the table. You gave the list box's rowsource as
Row Source: SELECT [VacType].[ID], [VacType].[Type],
[VacType].[Amount] FROM [VacType] ORDER BY [ID];
but now you say
I want to bind column 2 and 3 to pass to
Control Source: [Used] and [Type]
If you want to store [VacType].[ID] as Used and [VacType].[Type] as
Type, those are columns 1 and 2, not columns 2 and 3. (In VBA, they
would be listbox.Column(0) and listbox.Column(1)).
Also, reread what I said above about storing values that can be
retrieved by lookup whenever you want them. If ID is the primary key of
the VacType table, so that ID determines Type, then for most purposes it
is unwise to store both ID and Type in your form's recordsource table.
You would normally store only ID, and look up the Type associated with
that ID whenever you need it. As I said, there are circumstances when
you wouldn't want to do this -- as for eample, when the Type for a
particular ID may change in the future, and you want to record what that
Type is *now* -- but be sure that you understand the difference between
the two approaches.
Now, I'm assuming that the form containing this list box is bound to the
table where you want to store the selected values. If by this statement
....
I need to get both columns in the list box to pass to the to two other
columns on a seprate table
.... you mean you want to store the results in some other table entirely,
then the list box can't be bound at all, and you have use code to insert
or update a record in that other table. However, that would be a very
unusual arrangement.
