Filtering subform from another subform

[Kipandrews06]

Filtering subforms is going to be the death of me lol. I'm sure there is something really simple I'm missing or overlooking, but I need some help. Here is my problem:

My Database Info:
I have a main form that is unbound called "frmOpenOrders"
I have 2 subforms in it, Subform1 is called "frmOpenOrdersSubform" and is in datasheet view
Subform2 is called "frmProductOrdersSubform" and is also in datasheet view

So when I open the main form I have a list of all the "open" orders in subform1 for orders that have just been placed or haven't shipped yet. One of the fields in the subform1 I have is titled Products, it says "View" in each record and I want to make it a hyperlink basically (not an actual hyperlink but I have it set to be displayed as one) so when you click on "View" on a particular record, I want it to filter the subform2 which will show just the products that were ordered related to the record (order #) you click on. I have tried what seems like a gazillion combinations of things and just cant figure it out. The field that both of these subforms (and their queries) are linked to is called "OrderID". I've tried:

 Private Sub txtProducts_Click()

[Forms]![frmOpenOrders]![frmProductOrderDetailsSubform].[Form].Filter = "[OrderID] = " & Me.OrderID
[Forms]![frmOpenOrders]![frmProductOrderDetailsSubform].[Form].FilterOn = True




End Sub

I've tried other combinations on the filter = line such as

 "[OrderID] = '" & Me.OrderID & "'"

All to no avail. I have a feeling its going to be something simple that I overlooked, but was hoping someone could tell me what I'm doing wrong? Currently with the above code, it shows at the bottom of the subform2 that it is "filtered" but it's not showing any records, it's just blank. I'm not sure if I need to do unbound textboxes on the main form and/or subforms to reference everything to link them together or if I can use something similar to what I've posted? and FYI, I'm still pretty new to access, vba and more complicated programming, so I'm trying my best to learn what I can, so please be patient with me if I don't understand something.

Thank you!
Kip

[Kipandrews06]

One last thing, here is a screenshot of my form/subforms to give you a better visual of what I'm trying to do.



so in this example, when clicking on the "view" link for order #3, it would only show Order #3 product records in subform2.

[mike60smart]

Hi Kip
Best to make your Products Ordered Subform a Popup Form

When you click the View button the On Click event would use:-

DoCmd.OpenForm "Products Ordered", , , "[OrderID]= " & Me![OrderID]

[Kipandrews06]

Mike,
I tried that and it asked for a parameter value, if I typed in the order number I clicked on, it opens the subform, but it isn't filtered. And if I click cancel on the parameter value messagebox, it gives me a runtime error: '2501, "open form action was canceled"

[June7]

See if this is what you want https://www.fmsinc.com/MicrosoftAcce...edSubforms.asp

If you want to provide db for analysis, follow instructions at bottom of my post.

[Gicu]

Why not simply embed subform 2 (the product info) into the subform 1 (subform 1 becomes a "main" form and subform 2 its subform) and link them as master\child on the OrderID field? Then as you move from record to record in the orders datasheet the corresponding products will refresh without the need to click the View link.

Cheers,

[Kipandrews06]

Why not simply embed subform 2 (the product info) into the subform 1 (subform 1 becomes a "main" form and subform 2 its subform) and link them as master\child on the OrderID field? Then as you move from record to record in the orders datasheet the corresponding products will refresh without the need to click the View link.

@Gicu- Because I'm an idiot and didn't think of that lol. I tried that out and it worked perfectly. I did however get the original way to work too with Mike S's help. Thank you!