Link to home
Start Free TrialLog in
Avatar of WillbrosIT

asked on

Filter records based on data in a sub form

I have written code to filter based on all fields (about 40 of them) and this works ok.
My problem is I have a subform on the main form that is bound to a different table called notes.
Notes table only has two fields, employee_id and Note. the main screen is bound to a table with lots of fields employee_id, name, addr, work location, etc., the two table are relation tied via the employee_id field.
I have tried to build code to filter the note field and produce a current record set of all records on the main screen that match the filter, but is does not work. Can anyone help?
Flag of Kuwait image

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

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

Avatar of WillbrosIT


This is what I have:
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 Sub

    MsgBox Err.Description
    Resume Exit_Command212_Click
End Sub
Avatar of Helen Feddema
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

   Exit Sub

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

End Sub

Open 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?

yes, see the file. sbuform.doc
Flag of Kuwait image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks to everyone for your help.
I think I can make something work from your example, a special thanks for your time.