query in related tables

  • Thread starter Thread starter Jan G. Thorstensen
  • Start date Start date
J

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).
 
Jan said:
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).
Click to expand...


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.
 
Back
Top