Filter records based on data in a sub form

You may try using the Link Master Fields and Link Child Fields Property as shown in the image attached.

Sincerely,
Ed
master-field.jpg

In your scenario, the Link Master Fields and Link Child Fields valued would be employee_id.

Ed

This is what I have:
  sbuform.doc

My code:

Private Sub Command213_Click()
On Error GoTo Err_Command212_Click
Dim strin As String, ns As DAO.Recordset, nstr As String
'Dim stDocName As String
'Dim stLinkCriteria As String
'Dim dataflag
''*** notes field

Set ns = Me.Notes_subform.Form.RecordsetClone

strin = InputBox("Enter your search string, then click OK.", "Search by KeyWord Notes Field Only")

nstr = "[" & ns(1).Name & "] like '*" & strin & "*'"

'If Option190.Value = False Then
'    If Len(nstr) > 2064 Then
'    MsgBox "You can only enter 14 chr's to search  on. Please try again.", , "Search String to long."
'    GoTo Exit_Command212_Click
'    End If
'End If
'If Option190.Value = True Then
'    If Len(nstr) > 2064 Then
'    MsgBox "You can only enter 45 chr's to search  on. Please try again.", , "Search String to long."
'    GoTo Exit_Command212_Click
'    End If
End If
Me.Notes_subform.Form.Filter = nstr
Me.Filter = nstr
'MsgBox str
Me.Notes_subform.Form.FilterOn = True


'With Me.Notes_subform.Form
'.Filter = nstr
'.FilterOn = True
'End With

'    If Me.Notes_subform.Form.RecordsetClone.EOF Then
'    'If Me.RecordsetClone.EOF Then
'    If Option190.Value = True Then dataflag = 1
'         MsgBox "No Matching Record(s) Found"
'         DoCmd.Close acForm, Me.Name   'close the form
'         stDocName = "Disclosure Questionnaire"
'         DoCmd.OpenForm stDocName, , , stLinkCriteria
'         MsgBox "Note: The database defalted back to the Manual Data."
'         Exit Sub
'
'    End If



   
Exit_Command212_Click:
    Exit Sub

Err_Command212_Click:
    MsgBox Err.Description
    Resume Exit_Command212_Click
   
End Sub

I would dump the subform and just place an unbound combo box in the form header, for selecting a value for filtering and/or searching.  A main form/subform setup is not what you need here.  Here is some standard code for searching for a record usign a value selected in a combo box; it could also be used for creating and applying a filter.

Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Select allOpen in new window

I don't think I can dump the sub form. The users enter notes about an employee that is the current record in the main table, but they replace that entire table every week with a new download of employee data. If notes were creaded about a given employee_ID number and that numbers showes up in the new data, they want the old notes to be there when that record is selected.  

So, the user would like to filter the notes field, say looking for: "gets hurt on every job" and reduce the main record set down to only the records that have that in the notes field. I don't see how a combo box would solve this, but I don't see a lot of things these days!! lol

You guys give up on me?

If the Notes field is a text field, you could have a combo box with that field (from the subform record source) as its row source, and use the selected value to filter the main form.

I get what you are saying, but is it not possible to search/filter records from a text box on a sub form?? Must be something I'm missing....

Can you do a screenshot of the Main Form with the subform on it?

Ed

yes, see the file. sbuform.doc

thanks to everyone for your help.
Mindsuperb,
I think I can make something work from your example, a special thanks for your time.