binding fields

I have a "products" table with fields [product id] and [description],
I want another table: "orders" with fields [customer] [product id]
[description] and [qty].
how do I maintain this duplicity where I want each product ID and
description match?
Now I use the lookup field and recieve the product id from a list box.
can I fill the two fields in one list box?

As a matter of fact the problem occurs when I use the orders as a
subdatasheet in a form, I want to be able too see the description of
each product ID.


I want to thank very much to the one who chooses to answer this
question.

yuval

First question is whether you really want a Description field in the Orders
table.

If Orders.[Product ID] is a required field, and Orders.Description must
always match Products.[Product ID], then omit the Description field from the
Orders table.

On the other hand, if you want to be able to type in any old description in
an order, and it does not have to match the description in the products
table, then keep the Description in the Orders table. Use a combo box for
Product ID in the Orders, and give it these properties:
Column Count 2
Bound Column 1
RowSource SELECT [Product ID], [Description] FROM Products;
After Update [Event Procedure]
Click the Build button (...) beside the After Update property.
Access opens the code window.
Add this line between the "Private Sub ..." and "End Sub" lines:
Me.[Description] = Me.[Product ID].Column(1)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"yuval" <a_yuval@aquanet.co.il> wrote in message
news:2cec37bf.0401290530.4f564851@posting.google.c om...[color=blue]
> I have a "products" table with fields [product id] and [description],
> I want another table: "orders" with fields [customer] [product id]
> [description] and [qty].
> how do I maintain this duplicity where I want each product ID and
> description match?
> Now I use the lookup field and recieve the product id from a list box.
> can I fill the two fields in one list box?
>
> As a matter of fact the problem occurs when I use the orders as a
> subdatasheet in a form, I want to be able too see the description of
> each product ID.
>
>
> I want to thank very much to the one who chooses to answer this
> question.
>
> yuval[/color]

Thank you allen for your reply,

However, my orders table is a subdatasheet in a form.
After I entered a few rows, How can I see the description of each
product?

thanks a lot for the time of who ever answers.

yuval


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

For a subdatasheet (as distinct from a subform), you could use a combo box
with 2 columns, and set the ProductID column to zero width.

Alternatively, you could specify a query as the subdatasheet. A query
containing the main table outer joined to the Product table could display
the Product.Description in another text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"yuval amit" <a_yuval@aquanet.co.il> wrote in message
news:401ccb8e$0$70307$75868355@news.frii.net...[color=blue]
> Thank you allen for your reply,
>
> However, my orders table is a subdatasheet in a form.
> After I entered a few rows, How can I see the description of each
> product?
>
> thanks a lot for the time of who ever answers.[/color]