Filter records based on data in a sub form

WillbrosIT
WillbrosIT used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Author

Commented:
This is what I have:
  sbuform.doc
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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
Top Expert 2009

Commented:
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

Open in new window

Author

Commented:
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.  

Author

Commented:
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

Author

Commented:
You guys give up on me?
Top Expert 2009

Commented:
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.

Author

Commented:
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

Author

Commented:
yes, see the file. sbuform.doc
WillbrosIT:

You may see the attached file. This might not what you want to achieve but I am sure you can get an idea from it.

Sincerely,
Ed
ExpertsExchange.zip

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial