Lookup within a Table/Query/Form

[Guest]

I am trying to figure out how to have a Pull-Down box on a Form that, when
and item is selected from it, will update a dozen or so fields associated
with it.

For example:

A pull down box called "Customer" that allows the user to select data from a
list of Customers. When an item is selected from this list, I want Access to
gather the associated details for said Customer (i.e. Address, Phone#, etc),
and populate a group of fields on the Form with that information. When the
"Customer" field is changed again, the populated fields should change again.

Also, I assume that it will be the same general concept, but I would like
multiple lines on the Form with the same capability, all labeled "Customer".

Thanks in advance!

 

[Wolfgang Kais]

Hello Matt.

I am trying to figure out how to have a Pull-Down box on a Form that,
when an item is selected from it, will update a dozen or so fields
associated with it.

For example:

A pull down box called "Customer" that allows the user to select data
from a list of Customers. When an item is selected from this list,
I want Access to gather the associated details for said Customer
(i.e. Address, Phone#, etc), and populate a group of fields on the
Form with that information. When the "Customer" field is changed
again, the populated fields should change again.

Also, I assume that it will be the same general concept, but I would
like multiple lines on the Form with the same capability, all labeled
"Customer".

Thanks in advance!

You can base the form on a lookup query:
Create a table with one column: CustomerID (appropriate dadatype)
Then create a query that joins this table with the real customer table.
This query should contain the field of the new table and all other
fields of the contomers table that are of intererst for your form.
Then, base the form on this query and lock all fields but one: the
one you want a user to select a customer in. That's all.

 

[John Vinson]

I am trying to figure out how to have a Pull-Down box on a Form that, when
and item is selected from it, will update a dozen or so fields associated
with it.

For example:

A pull down box called "Customer" that allows the user to select data from a
list of Customers. When an item is selected from this list, I want Access to
gather the associated details for said Customer (i.e. Address, Phone#, etc),
and populate a group of fields on the Form with that information. When the
"Customer" field is changed again, the populated fields should change again.

Also, I assume that it will be the same general concept, but I would like
multiple lines on the Form with the same capability, all labeled "Customer".

Thanks in advance!

Why would you WANT to store that information redundantly in a second
table!? You don't. It should be stored in the customer table, and ONLY
in the customer table.

You can *display* it on the form by including the fields in the Combo
Box's RowSource query; set the column count to the number of fields
that you want to display, and the ColumnWidths property to set most of
them to zero (just displaying enough in the combo itself to
unambiguously select the customer). You can then put textboxes on the
Form with control sources

=comboboxname.Column(n)

where n is the *zero based* index of the field you want to see.

John W. Vinson[MVP]

 

[Guest]

Why would you WANT to store that information redundantly in a second
table!? You don't. It should be stored in the customer table, and ONLY
in the customer table.

You can *display* it on the form by including the fields in the Combo
Box's RowSource query; set the column count to the number of fields
that you want to display, and the ColumnWidths property to set most of
them to zero (just displaying enough in the combo itself to
unambiguously select the customer). You can then put textboxes on the
Form with control sources

=comboboxname.Column(n)

where n is the *zero based* index of the field you want to see.

John W. Vinson[MVP]

I guess I wasn't being quite clear enough. Sorry for that.

I *AM* only basing the data on a single table: "Customer". However, the Form
I am using is based on another table - "Salesman". I want to be able to
select up to four "Customers" to be assigned to each "Salesman", selected
from the table but referenced in the "Salesman" Table so that the "Salesman"
table keeps the assigned "Customer"s. I need the information in "Customer" to
autopoplulate fields on the form based on the customer that is selected from
the "Customer" table. I have already assigned four fields in the "Salesman"
Table to lookup their values based on the "Customer" table, but I need to
know how to get the rest of the data for the given "Customer" to show up on
the Form based on what "Customer" is selected for each of the four slots on
the "Salesman" form.

My Access knowledge is limited, so please don't assume knowledge of any
concepts in explanations - it'll make my life a lot easier if you pretend I
know nothing - that way the things I do know I can ignore, and the things I
don't will be explained!

 

[John Vinson]

I am using is based on another table - "Salesman". I want to be able to
select up to four "Customers" to be assigned to each "Salesman", selected
from the table but referenced in the "Salesman" Table so that the "Salesman"
table keeps the assigned "Customer"s. I need the information in "Customer" to
autopoplulate fields on the form based on the customer that is selected from
the "Customer" table. I have already assigned four fields in the "Salesman"
Table to lookup their values based on the "Customer" table, but I need to
know how to get the rest of the data for the given "Customer" to show up on
the Form based on what "Customer" is selected for each of the four slots on
the "Salesman" form.

My Access knowledge is limited, so please don't assume knowledge of any
concepts in explanations - it'll make my life a lot easier if you pretend I
know nothing - that way the things I do know I can ignore, and the things I
don't will be explained!

If each Customer can be assigned to several Salesmen, and each
Salesman can interact with several Customers - *you need a new table*.

The Customer table should have a unique CustomerID, and *NOTHING*
about salespeople. The Salesperson table should have a unique
SalespersonID and *nothing* about customers.

Instead, you should have a SalesAssignment table with fields for
CustomerID and SalespersonID. If one salesperson gets five customers
(they might!) you'll add five records to this table.

You can DISPLAY - not store!! - the customer data on the form; if you
have a subform bound to the CustomerID field in the SalesAssingment
table, you can include whatever fields you want in the Combo's
rowsource query. On the form you can put textboxes with control
sources like

=cboCustomerID.Column(n)

where cboCustomerID is the name of the combo box, and n is the zero
based index of the field you want to see.

John W. Vinson[MVP]