Filtering subform data off of textbox entry on parent form

Question

I have a rather simple Access 2013 db, made up of two tables, a query, form and subform. The tables are tblDeliveries and tblCustomers. Query is qryDeliveriesFull. Form is frmCustomers and sbfmDeliveries. frmCustomers is a splitform and sbfrmDeliveries is in this form. All Customer information, Name, Address is on the parent form and all the Delivery information is on the Subform. The subform is just a single form with textboxes for information. The Datasheet portion of the parent form is simply a way for me to see ALL the orders in the DB at one time. In the heading of the main form is a search box with the following code:

Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)

On Error GoTo errHandler

Dim filterText As String

If Len(txtNameFilter.Text) > 0 Then
   filterText = txtNameFilter.Text
   Me.Form.Filter = "[tblDeliveries].[DeliveryID] LIKE '*" & filterText & "*' OR [tblCustomers].[FirstName] LIKE '*" & filterText & "*'"
   Me.FilterOn = True

   txtNameFilter.Text = filterText
   txtNameFilter.SelStart = Len(txtNameFilter.Text)

Else

   Me.Filter = ""
   Me.FilterOn = False
   txtNameFilter.SetFocus

End If

Exit Sub

errHandler:

MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."

End Sub

When a user enters information into txtNameFilter, the datasheet filters out to the records containing that information, and the textboxes on the parent form populate with the information from the first record under that persons name. What I can't figure out is how to get the textboxes that are in the the subform to change as well. Example: customer john has 20 orders. Looking for 1 specific order number so I type that order number into txtname filter. Datasheet on parent from filters to that order, but subform, although the parent form correctly selected the appropriate name and went to a record for john it did not filter all the way down to the appropriate order. Not sure if that is because the DeliveryID is on the subform or if it is something else. I've searched around, but can't figure out how to get it do what I'd like it to, which is that the information on the subform also filters according to the text put into txtNameFilter.

Normally, subform filter is controlled by Master/Child Links properties. Only records associated with primary key on main form would display on subform, which is this case appears to be customer ID. If you want to do additional filter on subform, that means applying criteria to Filter property of subform or GoToRecord code. — June7

Anthony Griggs Anthony Griggs · Accepted Answer · 2020-05-22T15:47:24

You could follow suit by likewise filtering the subform:

   Me.Form.Filter = "[tblDeliveries].[DeliveryID] LIKE '*" & filterText & "*' OR [tblCustomers].[FirstName] LIKE '*" & filterText & "*'"
   Me.FilterOn = True

   Me.[SubformName].Form.Filter = "What ever the subform filter would be here LIKE '*" & filterText & "*'"
   Me.[SubformName].FilterOn = True

...

Filtering subform data off of textbox entry on parent form

1 Answers