Entering info into one field & having the description auto fill.

[rbalkovec]

Hello: I might be completely confused as to what I'm talking about and may
not use the correct terminology.

I have a database that has several tables, but in particular, there are 2
tables in question.

1 - The first table is called "Products" and it holds the following structure:
ProductID (AutoNumber) - Primary Key
ProductName (Text)
UnitPrice (Currency)
ProductDescription (Text)
*I planned on typing my product descriptions in this table under
"ProductDescription"

2 - The second table is called "Order Details" and it holds the following
structure:
OrderDetailID (AutoNumber) - PrimaryKey
OrderID (Number)
ProductID (Number) - this is a look up combo box to the "Products" table and
it displays the acutal name of the product - not the number it's connected to
ProductDescription - this is the field I want to be automatically filled in
once the above information is typed in. I was hoping this would pull from
the "Products" table.
Quantity (Number)
UnitPrice (Currency)
Price (Currency)
Discount (Number)

So - if ANYONE can help me out on this - I would greatly appreciate it.
I've tried the SQL stuff, but I'm not real familiar with it at all. I'm
thinking it's some simple command that is typed in and I'm making this harder
than it really is?

PLEASE HELP!

 

[Christopher Robin]

Are you on a form? From what I understand, you have a combo box for
ProductsID, which is displaying the incorrect information, and
ProductDescription, which is displaying nothing.

With a combo box on a form, you can actually display both of these pieces of
information, or just the description. Humans typically don't like looking at
IDs. For your combo box, the Row Source would be a simple query "SELECT
ProductID, ProductDescription FROM Products." The control source would be
ProductID. (As far as DB design is concerned, you should only store the
description in one place.) The bound column would be 0, and then on the
Format tab, Column Count = 2, and Column Widths = 0", 1". This will hide the
ProductID and display the ProductDescription.

Hopefully, I haven't completely misunderstood, what you want, or totally
confused you.

 

[rbalkovec]

No - I'm not on a form - I'm in the tables themselves. 1 - Products and the
other 2 - Order Details. What I wanted to accomplish with this is to have my
product information in one table (ProductID, ProductName, ProductDescription)
and then have that information auto fill into the Order Details table. There
is a form and subform to enter in orders. So - for example, if I type in the
product name in the subform (which the control source is the table for Order
Details), the description will auto fill under product description in that
same subform only the control source for the description needs to come from
the Products table. Does that make any more sense?

In regards to your other question: The ProductsID is displaying the correct
information (name of the product) in the table and ProductDescription is
displaying the correct information - they just don't auto fill?

Do you have a way to receive databases to review them? Maybe I have
something completely haywire? I was just under the assumption that all the
"programming" needed to be put into the tables first and then the forms would
recognize that information?

Any other thoughts as to what I might be doing wrong?

 

[John W. Vinson]

Hello: I might be completely confused as to what I'm talking about and may
not use the correct terminology.

I have a database that has several tables, but in particular, there are 2
tables in question.

1 - The first table is called "Products" and it holds the following structure:
ProductID (AutoNumber) - Primary Key
ProductName (Text)
UnitPrice (Currency)
ProductDescription (Text)
*I planned on typing my product descriptions in this table under
"ProductDescription"

2 - The second table is called "Order Details" and it holds the following
structure:
OrderDetailID (AutoNumber) - PrimaryKey
OrderID (Number)
ProductID (Number) - this is a look up combo box to the "Products" table and
it displays the acutal name of the product - not the number it's connected to
ProductDescription - this is the field I want to be automatically filled in
once the above information is typed in. I was hoping this would pull from
the "Products" table.
Quantity (Number)
UnitPrice (Currency)
Price (Currency)
Discount (Number)

So - if ANYONE can help me out on this - I would greatly appreciate it.
I've tried the SQL stuff, but I'm not real familiar with it at all. I'm
thinking it's some simple command that is typed in and I'm making this harder
than it really is?

The ProductDescription field should simply NOT EXIST in the Order Details
table.

Relational databases use the "Grandmother's Pantry Principle" - "A place - ONE
place! - for everything, everything in its place". If the OrderDetails table
(hint: don't use blanks in table or field names) contains a ProductID, that is
*all* you need from the Products table.

Your users should never see the table datasheets in any case; they will be
interacting with the data via Forms. On the Form you can use a combo box bound
to the ProductID but showing the description; the users won't need to know,
see, or ever type the Productid, just select a row from the combo box.

Take a look at the Orders form in the Northwind sample database. It does
exactly what you're trying to accomplish.