Order Main Form displays a record for each Order Detail Subform Record 1

[AaronMV]

I have a main form "frmProcessUnfilledOrders". This form has a subform named "qryProcessUnfilledOrders subform". In theory, I am supposed to see the main order record from the Orders table in the main form, with each item from the OrderDetails table neatly displayed below in the subform in datasheet view. Here is the problem: My main form displays a record for each subform item record. Thus, if the order detail subform has 3 items on it, the main form displays 3 times, with the 3 order details items properly itemized on the subform in each. I think that the snafu is in my SQL, so here it is:

SELECT tblOrders.OrderID AS tblOrders_OrderID, tblOrders.CustomerID, tblOrders.CSID, tblOrders.Order_date, tblOrders.Delivery_date, tblOrders.Filled, tblOrders.AccountID, tblOrders.Staff_Requestor, tblOrders.Recipient, tblOrderDetails.OrderDetailID, tblOrderDetails.OrderID AS tblOrderDetails_OrderID, tblOrderDetails.ProductID, tblOrderDetails.Quantity, tblOrderDetails.Backordered, tblOrderDetails.OrderID, tblOrders.Order_Notes
FROM tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderID = tblOrderDetails.OrderID
WHERE (((tblOrders.Filled)=False));

Any assistance is greatly appreciated.

AaronMV

 

[PHV]

The main form RecordSource should not select anything from tblOrderDetails.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AaronMV]

OK, sorry for being dense. I removed the OrderDetails fields from the query that the main Order form was using as RecordSource. I then created a separate query for the subform RecordSource with the right fields, but now when the form loads, I get a parameter prompt for OrderID. If I enter a valid OrderID, the form displays properly. Naturally, I want that OrderID to be passed to the subform by the main form OrderID field, not a parameter prompt. I hope this makes sense.

AaronMV

 

[PHV]

Did you link the subform to the mainform ?
Anyway, have a look at the Nothwind sample database furnished by MS with access.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AaronMV]

Thanks, PH. I will be less brain-dead next time.