Filtering the sub sub form

So I've built a database that looks like this:

tblCustomers: customerID, firstname, lastname, outstandingBal
tblOrders: orderID, customerID, receivedDate, dueDate, comments
tblOrderDetails: orderID, productID, quantity, notes, unitPrice
tblProducts: productID, name, description, unitPrice

Then I made a form that looks like this:

frmOrders - subfrmOrders - subsubfrmOrders

With frmOrders being a single sheet and the other two being datasheet.

Out the 3 forms here: The one that is connected to the Customer table is frmOrders, the subfrmOrders is connected to the Orders table, and then the subsubfrmOrders is connected to the Order Details form.

What I've done so far is to filter the Orders table depending on the combobox value of the Customer, so I only see orders for that customer. However, I can't get the Order Details to filter down to only the order that I expand by clicking the + on the left side. I set the record source of the subsubform to:

SELECT tblOrderDetails.orderID,
tblOrderDetails.productID,
tblOrderDetails.quantity,
tblOrderDetails.notes,
tblOrderDetails.unitPrice
FROM tblOrderDetails
WHERE tblOrderDetails.orderID=[Forms]![subfrmOrders]![txtOrderID]

With txtOrderID being the field name of the order ID in the subform. When I click on the +, I get a little pop up box asking me to input the parameter value for txtOrderID. I've set the subsubform to Requery in the Change and AfterUpdate event procedures of txtOrderID, however, nothing's working. How can I fix this?

That's because your reference is invalid. Subforms do not exists as forms and cannot be referred to as forms - this is why they are subforms.

To construct a reference the easiest thing is to make a textbox, in the control source click on the ... and select expression builder, and then navigate to the control in question on an open form ... paste, ok, and done .. you have a recipe for a reference.

Hi spikepl, thanks for the reply. I'm not completely understanding what you mean...where should I be making this new textbox and which Control Source am I pointing to? Sorry, I'm new to Access and this might be a silly question.

A subform is a form hosted in a control on a parent form, and linking the data sources can be done somewhat automatically. The control on the parent form has two properties you should check out, namely, LinkMasterFields and LinkChildFields. You can find these on the Data tab of the subform control's property sheet in design view.

So if the parent form is subfrmOrders, then the LinkMasterFields AND the LinkChildFields properties of the subform control--showing OrderDetails--should both be orderID, since that is the field on which those tables are joined.

And then the RecordSource of your subsubformOrders should just be "tblOrderDetails", since the linking and filtering will now be automatic.

Hope this helps,

Wow...you literally solved a problem that had me stumped for the past 4 days. I was beating myself up trying to figure this out. Thanks so much!

You're welcome, glad to help!