Search Engine - VBA - Filtering a subform datasheet

[watercooled81]

Search Engine - Filtering a datasheet subform from a textbox on main form.

Hopefully someone can help, as I am trying to make a search function in one of my databases.

I have a Main form (named Main) which has a number of subforms on it. I have put the subforms on different pages. The form I am interested in, is on a page called Search and the subform is called frmSearchResultsSubForm

Within the main form I have a text box called txtSearchString and a combo box called
cboSearchField. The values of the combo box match the headers of each column of the subform. The headers are FirmNumber, FirmName, CustomerSurname, Date

I am hoping to be able to type all or part of a name, select the relevant field then hit the search button to filter the subform.

I have tried various VBA but nothing worked.

Below is my latest attempt, before I gave up trying to figure it out myself. ?

Private Sub cmdSearch_Click()

If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter your search requirement."

Else

'Filter frmSearchResultsSubForm based on search criteria

Me.frmSearchResultsSubForm
.Filter = " & cboSearchField.Value & Like & txtSearchString.Value & "
.FilterOn = True



MsgBox "Results have been filtered."

End If

End Sub

 

[PHV]

A starting point (for text fields):

...
    'Filter frmSearchResultsSubForm based on search criteria
    With Me!frmSearchResultsSubForm.Form
        .Filter = "[" & Me!cboSearchField & "] Like '" & Me!txtSearchString & "*'"
        .FilterOn = True
    End With
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886