Avatar of WillbrosIT
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?
Microsoft Access

Avatar of undefined
Last Comment
WillbrosIT

8/22/2022 - Mon
MINDSUPERB

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

Sincerely,
Ed
master-field.jpg
MINDSUPERB

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

Ed
WillbrosIT

ASKER
This is what I have:
  sbuform.doc
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
WillbrosIT

ASKER
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
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

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

WillbrosIT

ASKER
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.  
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
WillbrosIT

ASKER
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
WillbrosIT

ASKER
You guys give up on me?
Helen Feddema

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
WillbrosIT

ASKER
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....
MINDSUPERB

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

Ed
WillbrosIT

ASKER
yes, see the file. sbuform.doc
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
MINDSUPERB

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
WillbrosIT

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