Combo Box, How to display multiple column values on form?

[Julian Ganoudis]

Using a combo box,is it possible to have more than one
bound value on a form? Is it also possible to display all
the column values for the selected row on the form,
instead of just the one bound value?

For example:

I have a form called "Current Loans", it has two tables;
T_CurLoans and T_Contractor List. T_CurLoans is the
underlying table for the form. Using a combo box, I want
to display all the values for the selected contractor in
the list (T_Contractor List--contractor name, vendor#,
fax#, etc.), not just for selection, but for inclusion in
each record on the form. I can select a particular value
to bind to the form (i.e. contractor name), but I can only
select one value and only display the same value (which is
bound or added to the CurLoans table. How can I display
and add each column value of the selected row to the
form? Note, there can be many contractors for each loan
(record) and I want to include multiple combo boxes (say
up to 5) to display and record multiple contractors and
contact info on the form. Second note, alternately, if I
use a text box to display additional values after the
contractor name has been selected from the combo box, I
can't get a value in the text box (sometimes I get an
error, looking for the "name?"), I don't know how to link
the two tables, since there is no common identifier (like
customer ID in the Northwinds database) where I can link
the two tables and use a query as the underlying source.
(T_CurLoans uses an autonumber field for each new record)
TIA, Julian

 

[Ted Allen]

Hi Julian,

I think that you need a third table to join your loans to
the contractors. The loan table should be keyed by some
type of loan ID, and the contractor table keyed by
Contractor ID. Then, a third table would have fields for
Loan ID and Contractor ID (the combination of the two
would be the primary key)

In this third table, you set the lookup properties of
each field to allow you to use a combo box to lookup the
loan and the contractor. I usually set these in the
table even though I won't be using the table for data
entry because the settings will carry over to any forms
that you design in the future.

Then, in your form, insert a subform which uses a query
as the data source tying together the join table and the
contractor table (datasheet view is usually easiest, but
you can also use continuous form view). The link to the
main form will be via the loan ID reference in the join
table. In fact, you can then hide the Loan ID field in
the subform because Access will set it automatically when
you add a new record.

This will allow you to have a combo box to choose a
contractor, and automatically all other fields from the
contractor lookup table will be displayed. In addition,
it will allow you to enter an infinite number of
contractors for a loan, so you don't have to try to
determine how many fields to add. And, if you ever want
to pull up all of the loans for a contractor it will be
easy because you just filter your join table for the
Contractor ID, instead of having to search 5 or so
different fields.

Hopefully this will help. Post back if I misunderstood
your question or if you have any other questions.

-Ted Allen

 

[Julian Ganoudis]

You understood!...I'll give it a whirl and let you
know...thanks...julian