Drop Down / Combo Box

[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

 

[Jeff Boyce]

Alison

I may not be seeing the big picture ...

How is a builder (BuilderID) related to a VendorProduct?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[alison.justice]

The Builder provides all the pricing for each vendor that they use. It is
easier for me to connect the Builder with the Customer than the Vendor with
the Customer because I could have 60 different vendors

 

[Jeff Boyce]

Alison

"easier" is not a usual justification during normalization. If you want to
get the best use of Access' relationally-oriented features/functions, you
need to 'feed' it well-normalized data.

Have you looked into the entities and relationships in your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[alison.justice]

Yes, I have looked into it. I guess I'm not explaining what I am trying to
accomplish correctly.

I understand how the relationships and all that work, and I have set this up
correctly, I am just needing to know what the code is in order to return the
results I am needing to do.

Here let me try to explain this again.

I have a table Vendor Products that lists all the levels/colors/model
numbers that a builder supplies for a specific plan/community. The table
includes the following:

BuilderID
NeighborhoodID
PlanID
Color
Model
Style #
StyleName
Price

Each level can have up to twenty plus colors....i.e. carpet could have a
Level 1 that has thirty colors associated with that level..

So, when a customer comes in to select their colors, off of a pre-printed
worksheet (catalog) then the information needs to be entered into a customer
table, which narrows down the specific color that they chose. So here come
the Customer Table which has the following:

BuilderID (I have to have this because there is more than one Builder)
NeighborhoodID (I need this so as it can identify what specification level
the neighborhood gets)
CustomerID
CategoryID (i.e. carpet)
Level
COLOR (here is where I need the combo drop down to select what color which
will have the Model #, style #, style name that is associated with the color,
these are the fields that I need to fill into the other fields on the form so
the person that is filling out the form does not have to type all this
information).

The color is what will determine the rest of the fields. Does this make
sense.

Alison

 

[Jeff Boyce]

please see comments in-line below...

Yes, I have looked into it. I guess I'm not explaining what I am trying
to
accomplish correctly.

I understand how the relationships and all that work, and I have set this
up
correctly, I am just needing to know what the code is in order to return
the
results I am needing to do.

Here let me try to explain this again.

I have a table Vendor Products that lists all the levels/colors/model
numbers that a builder supplies for a specific plan/community. The table
includes the following:

Based on this description, more than one builder could "supply" the same
level/color/model, right?

BuilderID
NeighborhoodID
PlanID
Color
Model
Style #
StyleName
Price

I suspect that the products belong in a Product table, and the relevant
ProductID would show up in the Builder/Plan table. What is a
NeighborhoodID, and how is it different from the PlanID.

And I don't see anything in the above table that says WHAT has a color or
model or style.

Why both Style# and StyleName? If you know one, don't you know the other?
This argues again for a separate Products table.

Each level can have up to twenty plus colors....i.e. carpet could have a
Level 1 that has thirty colors associated with that level..

So, when a customer comes in to select their colors, off of a pre-printed
worksheet (catalog) then the information needs to be entered into a
customer
table, which narrows down the specific color that they chose. So here
come
the Customer Table which has the following:

BuilderID (I have to have this because there is more than one Builder)
NeighborhoodID (I need this so as it can identify what specification level
the neighborhood gets)
CustomerID
CategoryID (i.e. carpet)
Level
COLOR (here is where I need the combo drop down to select what color which
will have the Model #, style #, style name that is associated with the
color,
these are the fields that I need to fill into the other fields on the form
so
the person that is filling out the form does not have to type all this
information).

So the Product is known by CategoryID?

Note -- later versions of Access offer a "lookup" data type in the table
definition. Folks in the newsgroups here generally do NOT recommend using
it. There would NOT be a combobox in the table to chose Color. The
combobox belongs in a form.

Note2 -- a well-normalized relational table does not have the kind of
dependencies you imply with the explanation of COLOR above. Instead, you'd
use a query to join the ColorID in this table to a (lookup) table of Colors,
along with all the other related fields. That way, you aren't repeating the
data related to COLOR choice in each/every record.


\> The color is what will determine the rest of the fields. Does this make

sense.

Alison

If this explanation isn't helping, perhaps you'll need to repost your
question and see if you get any other answers...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[alison.justice]

Nevermind....I figured it out....the look up box (whatever you want to call
it) is going in a form, and yes I was using a query.....

Bottom NOTE #2 is what I am trying to do......

Thanks for your help anyway....guess I just didn't explain it correctly.

 

[Jeff Boyce]

Not sure there's a specific right/wrong way ... some work better than
others, though.

Consider posting your solution -- someone else may have the same question
and your solution may help them.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[alison.justice]

Okay will do, and thanks for your help I really do appreciate the advise.