Drop Down Box

[alison.justice]

I have a drop down box to look up a Builder Name on a form from another
table, I am wanting to be able to select the Builder ID and have the ID go in
one control and the name automatically go to the Builder name Control. Does
anyone know how to do this?

 

[alison.justice]

Actually the combo box is looking for the Builder in a separate table, and I
am trying to pull it into another table using a form.

 

[John W. Vinson]

Actually the combo box is looking for the Builder in a separate table, and I
am trying to pull it into another table using a form.

Why?

The builder name should exist in the builder table - AND NOPLACE ELSE.

You're using a relational database; use it relationally! Store the builder
name once, and once only, and then use Queries and Combo boxes and related
tools to look it up whenever you need it.

 

[alison.justice]

So if I have a table that I need to link a Builder to a specific Customer,
which the builder can be used more than once in the customer table, how do I
do that?

 

[John W. Vinson]

So if I have a table that I need to link a Builder to a specific Customer,
which the builder can be used more than once in the customer table, how do I
do that?

If each Customer can have only one Builder, just put a BuilderID field in the
customer table, as a link to the Builders table

If each Customer can have one or more Builders, and each Builder can work with
one or more Customers, you need a new table with a CustomerID and BuilderID
field (and perhaps other fields to describe what this builder does for this
customer).

You may need to do a little research about how relational databases work
beyond the very simplest one- or two-table scenarios: see
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

 

[Lee]

I believe what you are after is...
The combo can display one text i.e. the customer name, and save a different
value in the field i.e. the customer id.

1. the query that populates the combo box would have the customer id in
column 1 and the customer name in column 2
2. set the column count property to 2
3. Set the Bound Column to 1
3. set the column widths property to 0";2" (zero inches for the 1st column,
2 or the second) this will hide the display of the customer id, and show only
the customer name, resulting in the customer Id being the field bound to the
data and the name being the information shown. If you see

 

[alison.justice]

I already understand the relationships between different tables, I guess I
needed to put more detail in my question.

Here it goes:

I have a table called Builders which has the following fields

BuilderID
BuilderName

I have a table named Vendor Products which has the following fields:

BuilderID
CategoryID
CategoryDetailDescription
Color

I have a table called Customers which links the Builder to the customer
based off of the Builder ID, the customer table has the following fields:

CustomerID
BuilderID
Customer Last Name
Customer First Name
Address
City
Zip
Plan

Now what I am wanting to do is the Customer table is a table that is going
to be an input form. Basically after a customer selects the colors, I would
like to have a drop down box that will list all of the colors for a specific
category and when the color is selected it fill in all of the information
into other fields on the form that is related to the color.

I apologize for not being clearer in original posting, I've done this
before, but for some reason my mind has gone blank and I forgot what the code
is to do this.

Thank you

 

[alison.justice]

I already understand the relationships between different tables, I guess I
needed to put more detail in my question.

Here it goes:

I have a table called Builders which has the following fields

BuilderID
BuilderName

I have a table named Vendor Products which has the following fields:

BuilderID
CategoryID
CategoryDetailDescription
Color

I have a table called Customers which links the Builder to the customer
based off of the Builder ID, the customer table has the following fields:

CustomerID
BuilderID
Customer Last Name
Customer First Name
Address
City
Zip
Plan

Now what I am wanting to do is the Customer table is a table that is going
to be an input form. Basically after a customer selects the colors, I would
like to have a drop down box that will list all of the colors for a specific
category and when the color is selected it fill in all of the information
into other fields on the form that is related to the color.

I apologize for not being clearer in original posting, I've done this
before, but for some reason my mind has gone blank and I forgot what the code
is to do this.

Thank you