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!
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?
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?
Do more with
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
Sincerely,
Ed
master-field.jpg
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.Reco
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.Filt
Me.Filter = nstr
'MsgBox str
Me.Notes_subform.Form.Filt
'With Me.Notes_subform.Form
'.Filter = nstr
'.FilterOn = True
'End With
' If Me.Notes_subform.Form.Reco
' '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
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.Reco
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.Filt
Me.Filter = nstr
'MsgBox str
Me.Notes_subform.Form.Filt
'With Me.Notes_subform.Form
'.Filter = nstr
'.FilterOn = True
'End With
' If Me.Notes_subform.Form.Reco
' '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
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
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....
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial