How make subform independent of main form?

[Dave]

I have a form, say orders, and a subform, say parts.

I want the subform to display as a pick list. IOW I want it to display all
the parts not just the ones currently assigned to that order.

The easy way to do this is to just link the form and subform on the orderid
and use drop down boxes on the subform for the associated parts.

But I do not want to display drop down boxes, I want to see a scrollable
list with ALL parts Those parts that are currently assigned to the order
will have a marked checkbox.

I can get close to what I want todo by using the following query as the
recordsource of the subform:

SELECT partid, a.orderid, partname
FROM parts LEFT JOIN (SELECT partid, orderid FROM order_parts WHERE
orderid = txtorderid) AS a ON a.partid=parts.partid

This gives me a list of all parts in the database and those assigned to ths
particular order will have a non-NULL orderid value.

I embed this subform in the main for and set the subform's recordsource with
DAO based on the order displayed in the main form.

But the problame is, if I set the main form's recordsource as the orders
table, the subform gets filtered. In other words, the only records that
display are the ones assigned to that order. This filtering persists even
if I remove the values in the Link Child and Master field properties.

If I remove the main form's recordsource and select an order based on a drop
down box, the subform will display all parts (checked and unchecked) as I
desire (i.e. no filtering of parts based on the orderid),

So I guess my basic question is, what is cause this linkage between main and
sub forms and how can I break it?

 

[Michael Gramelspacher]

SELECT partid, a.orderid, partname
FROM parts LEFT JOIN (SELECT partid, orderid FROM order_parts WHERE
orderid = txtorderid) AS a ON a.partid=parts.partid

Subform recordsource maybe should be like:

SELECT partid, partname, (SELECT COUNT(* )
FROM order_parts
WHERE order_parts.partid = parts.partid
AND order_parts.orderid = Forms![Orders Form]![orderid]) > 0 AS InOrder
FROM parts;

 

[Albert D. Kallal]

But the problame is, if I set the main form's recordsource as the orders
table, the subform gets filtered. In other words, the only records that
display are the ones assigned to that order. This filtering persists
even if I remove the values in the Link Child and Master field properties.

This is kind of a bug I seen happen over the years.

The following code is thus what I had to use in this case:


Me.subGuides.Form.RecordSource = strSql
Me.subGuides.LinkChildFields = ""
Me.subGuides.LinkMasterFields = ""


So, *right* after you stuff in the sql, hammer out the linkchild/master
fields. (even if they are already blank, running the above code seems to fix
this). I don't know why this happens, but the above does fix the problem.

You might have to dump your assignment of the dao reordset to the form and
use the above (stuff in sql, and then hammer out the link child fields as
above...

 

[Michael Gramelspacher]

Thanks but that can't work; you can't have a WHERE clause ( SELECT count...>
0) in your SELECT list????

SELECT partid, a.orderid, partname
FROM parts LEFT JOIN (SELECT partid, orderid FROM order_parts
WHERE
orderid = txtorderid) AS a ON a.partid=parts.partid

Subform recordsource maybe should be like:

SELECT partid, partname, (SELECT COUNT(* )
FROM order_parts
WHERE order_parts.partid = parts.partid
AND order_parts.orderid = Forms![Orders Form]![orderid]) > 0 AS
InOrder
FROM parts;

Is that your rule?

 

[John W. Vinson]

Thanks but that can't work; you can't have a WHERE clause ( SELECT count...>
0) in your SELECT list????

Certainly you can; it's called a subquery.

 

[Dave]

Thanks but that can't work; you can't have a WHERE clause ( SELECT count...>
0) in your SELECT list????

SELECT partid, a.orderid, partname
FROM parts LEFT JOIN (SELECT partid, orderid FROM order_parts
WHERE
orderid = txtorderid) AS a ON a.partid=parts.partid

Subform recordsource maybe should be like:

SELECT partid, partname, (SELECT COUNT(* )
FROM order_parts
WHERE order_parts.partid = parts.partid
AND order_parts.orderid = Forms![Orders Form]![orderid]) > 0 AS
InOrder
FROM parts;

 

[Dave]

I'll be a monkey's uncle, it does work.

In Northwinds db:

SELECT Products.ProductID, Products.ProductName
,(SELECT count(*)
FROM [order details] od
WHERE od.productid= Products.productid
AND od.orderid=10285)>0 as a
FROM products;

....returns the required result set.

But that same query throws an error in T-SQL (SQL Server) ....

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '>'.

I know I am in an Access forum and my question pertains to an Access issue.
But can you confirm this syntax is unique to Access SQL? .

My sincere apologies to Michael Gramelspacher for dismissing his advice
without testing first.

And thanks for everyone's help.

 

[Michael Gramelspacher]

In Northwinds db:

SELECT Products.ProductID, Products.ProductName
,(SELECT count(*)
FROM [order details] od
WHERE od.productid= Products.productid
AND od.orderid=10285)>0 as a
FROM products;

...returns the required result set.

But that same query throws an error in T-SQL (SQL Server) ....

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '>'.

I get the same error. Maybe this is a question for the experts
in the m.p.s.programming newsgroup. Or maybe somebody here has the answer.

 

[John W. Vinson]

But that same query throws an error in T-SQL (SQL Server) ....

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '>'.

From Books Online 2005 reference
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/9c1d634c-fc9f-42e8-ae6f-20b350ef5c90.htm:


SELECT Ord.SalesOrderID, Ord.OrderDate,
(SELECT MAX(OrdDet.UnitPrice)
FROM AdventureWorks.Sales.SalesOrderDetail AS OrdDet
WHERE Ord.SalesOrderID = OrdDet.SalesOrderID) AS MaxUnitPrice
FROM AdventureWorks.Sales.SalesOrderHeader AS Ord

 

[Dave]

Thanks John

Yes that works if we add the order id reference.

Here it is with Northwinds...

SELECT Products.ProductID, Products.ProductName
,(SELECT Max(productid)
FROM [order details] od
WHERE od.productid= Products.productid
AND od.orderid=10285) as a
FROM products;

But it looks like in T-SQL you can't use a comparison operator (> < = ) with
a subquery in the SELECT list like you can with Access SQL.

 

[Paul Shapiro]

Try this one. The comparison has to be made in the main query's Where
clause, just like it would be for any other column in the main query. The
subquery can have its own Where clause, but you can't put a condition on the
subquery result in the Select clause which is defining the column via the
subquery.

SELECT
Products.ProductID
, Products.ProductName
,( SELECT count(*)
FROM [order details] od
WHERE od.productid= Products.productid
AND od.orderid=10285
) as aNewColumn
Where aNewColumn > 0
FROM products;

Since the subquery appears to be checking for the existence of a product in
that order, and not actually counting anything, you could more clearly use
the subquery in the main query's Where clause:
SELECT
Products.ProductID
, Products.ProductName
Where Exists
( SELECT *
FROM [order details] od
WHERE od.productid= Products.productid
AND od.orderid=10285
)
FROM products;

Or do it with a join instead of a subquery:
SELECT
P.ProductID
, P.ProductName
FROM
products P
Join [order details] OD On OD.productid= P.productid
Where OD.orderid=10285

 

[Charles Wang [MSFT]]

Hi Dave,
Could you please elaborate why you think that you cannot use a comparison
operator with a subquery in the SELECT list?

The following query should work:
SELECT Products.ProductID, Products.ProductName
,(SELECT Max(productid)
FROM [order details] od
WHERE od.productid= Products.productid
AND od.orderid>=10285) as a
FROM products;

Thanks!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================