query in related tables

[Jan G. Thorstensen]

I have a table Orders which is related to table orderLines. This table
is then related to the table Products which in turn is related to
a Supplier table.

Orders --> orderLines --> Products --> Supplier

In my Form frmOdre, which have a subform frmSubOrderLines,
is it possible to have a text field that gets the Supplier name whith
a query string?

If so, can somebody suggest how the query string will look like?
Any help will be very much appriciated, thanks!


Jan
(Using Acess 2000 under Win98 SE).

 

[Marshall Barton]

I have a table Orders which is related to table orderLines. This table
is then related to the table Products which in turn is related to
a Supplier table.

Orders --> orderLines --> Products --> Supplier

In my Form frmOdre, which have a subform frmSubOrderLines,
is it possible to have a text field that gets the Supplier name whith
a query string?

If so, can somebody suggest how the query string will look like?
(Using Acess 2000 under Win98 SE).

Set the Products combo box's RwoSource query to something
like:
SELECT Products.PorductID,
Products.ProductName,
Suppliers.SupplierName
FROM Products INNER JOIN Suppliers
ON Products.SupplierID = Suppliers.SupplierID
ORDER BY Products.ProductName

Now set the combo box's BoundColumn to 1, ColumnCount to 3
and ColumnWidths to 0;1.5;0

With all that in place a text box can use the expression:
=thecombobox.Column(2)

Note that you can not use this approach to display the
supplier name in a continuous form. If OrderLines is a
continuous (sub)form, the supplier name would have to be
included in the subform's record source query by joining
something likethe above to the OderLines table.

 

[Jan G. Thorstensen]

Thank you for your help. I couldn't have solved this my selves, thanks.

Jan