Open a record in a new form from a list box with criteria of a subform

[shaunacol]

I have a search List box that lists all orders (customer details and their orders) the tables are related one to many (one customer has many orders). The issue is that the form that is being opened from the list box is CUSTOMER with a subform of ORDER. My list box was originally linked to the form that is opening through ClientID. But of course this causes an issue if the client has more than one order. I tried my best to make the listbox linked to the OrderID in the ORDER subform but it does not recognise it and then just brings up a blank form. Is it possible for a list box to reference a subform or am fighting a losing battle?
Here is the 'where' criteria I used in my macro to try and reference the subform: [Forms]![Frm_OrderClient1]![Frm_OrderSub1]![OrderiD]=[Forms]![Frm_FindBooking]![SearchList]



But I just get a blank form. Any help appreciated.

[ItsMe]

... The issue is that the form that is being opened from the list box is CUSTOMER with a subform of ORDER....

Your where criteria should be filtering the Main Form not the Sub Form. The sub form should be dependent on the main form via the Main/Child link fields.

Where
[Forms]![Frm_OrderClient1]![OrderiD]=903

[shaunacol]

Yes unfortunately I cant do that as customer is the main form and they have many orders so access doesn't know which customer order to display and therefore displays nothing (I want that particular order and not all of them). Ive figured it out now and I just use the Order form as my main form and put the customer as a subform. Since the record is already created and im just going back to it for any edits this doesn't seem to be an issue and now my list box knows which ORDER to open for that customer. Thanks for your help.

[ItsMe]

I have forms with multiple Sub Forms that do not have any Master/Child Link properties. You do not have to have Master/Child properties defined. What I do is use VBA to dynamically assign the Recordsource of the SubForm. In your situitation, I might have VBA consider the ListBox in the other form and store the ListBox value(s) in variables. Then, I could use the variables in the SQL for the subform. I would probably go through the extra step of using variables to avoid dependencies on the form with the ListBox.

[shaunacol]

Thanks for your suggestion, I don't think my coding is good enough to achieve this but it looks like a more robust way. Its all working perfectly now so its ok for me