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]
