The specifics will depend on the data you are setting up.
Normally you would have something like this:
- a table of colors (one record for each color that exits), ColorID primary
key
- a table of products (one record for each thing you sell), ProductID p.k.
- a table for orders (one record for each sale)
- a table of order details (one record for each product in an order.)
If a product comes in one particular color only (i.e. you have a different
product code if it's a different color), then you have a ColorID in the
products table. Then when you enter an order, you automatically know what
color it is, and you use the query (as shown previously) to determin the
color.
If one product comes in multiple fixed colors, you would have a ProductColor
table (one record for each valid combination of product and color. The
OrderDetail table would have fields for ProductID and ColorID, it would
relate to the ProductColor table (not directly to the product table), so
that only valid combinations can be entered.
If that's not what you are doing, I'm not clear on what you need.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
bobinmil said:
Hi and thank you very much for responding to my question. The
ColorNameTBL
has all of my products listed in it. The ColorLogTBL only has what we
currently have in stock, so it is a much shortened list. When I create a
new
record in the ColorLogTBL and enter a GINNumber into the new record I'd
like
the associated name to auto fill-in the ColorName text box on the form and
then of course in the ColorLogTBL. I hope that makes sence... The
ColorLogTBL is fluid; records will frequently be added and deleted as what
we
have in stock changes.
--
bobinmil
Allen Browne said:
This can probably be done with a query, or a combo box.
I take it that ColorNameTBL has only a few color names, and ColorLogTBL
contains all your products. ColorNameTBL has a ColorID primary key;
ColorLogTBL will have a ColorID field as well (so you can record what
color
that product is.)
If that's how it works, create a query that uses both tables.
Double-click
the line joining the 2 tables in the upper pane of query design. Access
pops
up a dialog offerring 3 choices. Choose the one that says:
All records from ColorLogTBL, and any matches from ColorNameTBL.
Drag the * from ColorLogTBL (in the upper pane of query design) into the
grid, and the ColorName field from ColorNameTBL. The query now shows all
fields from ColorLogTBL and also the ColorName. Save the query.
Open your form in design view, and set the RecordSource property of the
form
to the query you just saved. You can now add a text box to show the
ColorName field.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
I have two tables (ColorNameTBL and ColorLogTBL) and one continuous form
for
ColorLogTBL. The ColorNameTBL has all of my product numbers and
associated
names. The ColorLogTBL is used to record current stock, so, when I
enter
a
number in my form that matches a number in ColorNameTBL, I would like
the
associated name to be filled in on the form. I tried conditional
formatting
the ColorName text box on the form, using an expression that I found in
Help,
but it didn’t work. Can someone help? Is there a better way?
DLookup("[ColorName]", "[ColorNameTBL]", "[GINNumber] = " &
Forms![ColorLogFRM]![[GINNumber])
