Filtering datasheet subform using criteria found in subdatasheet

[alikona]

Hi all,

Been playing around with a database and I am a bit stumped on figuring out how to do what I want to do. Maybe its not even possible, I've not have much luck using google to find other examples of this.

Setup details
Main form: frm_Orders (this form is used as a lookup form only, no data entry is allowed)
Subform: subf_Orders (displays data from tbl_Orders in datasheet format)
Subform control: CtrOrders
Subdatasheet: tbl_LineItems
Lookup table: tbl_PartNumber
Search combobox: PartSearch (lists all part numbers from tbl_PartNumber)

tbl_Orders is linked to tbl_LineItems in a one-to-many relationship, linked by OrderID



On my main form, I have several drop down boxes that allow users to filter records in the subform, and I've gotten all these working as they are direct filters.
I would like to add an additional search combobox that allows users to search for PartNumber, which is a field in tbl_LineItems. I would like this to display the records in tbl_Orders that have line items for this Part Number, an additional requirement is to display all of the line items for that particular order, not just the ones that have the matching part number.
Any ideas? It’s not really a true filter so I’m unsure how to go about this.

[Micron]

I think this is what you want? I'm not going to copy and paste and pretend it's my work - especially since I don't use subdatasheets

https://stackoverflow.com/questions/...n-subdatasheet

[ranman256]

I use a combo box on the master form,(fed from a table)
in it is CAPTION, QRY
user picks the item (caption) shown, then the combo , bound to the query, resets the subform source:
subfrm.sourceobject = "query." & cboBox

this also works with TAB control.

[alikona]

I got it to work, ty for the direction.

For anyone else looking for a similar solution:

I created a query with the following SQL (also had to assigned my subdatasheet to this query):

SELECT tbl_Orders.*
FROM tbl_Orders INNER JOIN tbl_LineItems ON tbl_Orders.OrderID = tbl_LineItems.OrderID
WHERE (((tbl_Orders.OrderID)=tbl_LineItems.OrderID) And ((tbl_LineItems.PartNumber)=PartSearch));

And then I set the After Update event of my search dropdown box to:

Me.CtrOrders.Form.RecordSource = "qry_OrdersLineItems"

For my Filter Reset button, I added to the After Update

Me!PartSearch = Null
Me![CtrOrders].Form.RecordSource = "SELECT * FROM [tbl_Orders]"

Also, in case someone closes the form with the filter applied, I added this to the On Load event for the main form:

Me![CtrOrders].Form.RecordSource = "SELECT * FROM [tbl_Orders]"