Access search with vba - show results in subform without change RecordSource

Question

How can I show the results from my keyword search without change the RecordSource to my sql string? Reason is, if I change the RecordSource, the standard filter from the datasheet changes the tablename to the subform

Ok here is my sql query from my 7 tables:

SELECT table1.*, table2.*, table3.*, table4.*, table5.*, table6.*, table7.* FROM table1 LEFT JOIN (((table2 LEFT JOIN table3 ON table2.ID = table3.ID) LEFT JOIN table7 ON table3.ID = table7.ID) LEFT JOIN ((table4 LEFT JOIN table5 ON table4.ID = table5.ID) LEFT JOIN table6 ON table5.ID = table6.ID) ON table7.ID = table4.ID) ON table1.ID = table2.test;

here my search button:

Private Sub btnSearch_Click()
Dim sql As String
Dim query As QueryDef

' sql query for search with keywords
sql = " SELECT * FROM qryData WHERE " & BuildFIlter


'set the new sql as RecordSource
Me.sfrmSearch.Form.RecordSource = sql


'Delete the query if it already exists
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryExport"

'Set the query and save it in current database
Set query = CurrentDb.CreateQueryDef("qryExport", sql)
End Sub                                                                                                                                                         

Private Function BuildFIlter() As Variant
Dim varWhere As Variant

varWhere = Null 'Main Filter

' Check for LIKE test_Name
If Me.txtKeywords > "" Then
    varWhere = varWhere & " [test_name] LIKE '*" & Me.txtKeywords & "*' "
End If


' Check if there is a filter to return...
If IsNull(varWhere) Then
    varWhere = ""
End If 
BuildFIlter = varWhere 
End Function

now my export button:

Private Sub btnExport_Click()
Dim filterSQL As String
Dim sql As String
Dim query As QueryDef
Dim x As Integer

filterSQL = " SELECT * FROM qryData "

   If Me.sfrmSearch.Form.OrderBy <> vbNullString And Me.sfrmSearch.Form.OrderByOn = True And Me.sfrmSearch.Form.Filter <> vbNullString And Me.sfrmSearch.Form.FilterOn = True Then
     filterSQL = filterSQL & " WHERE " & Me.sfrmSearch.Form.Filter & " ORDER BY " & Me.sfrmSearch.Form.OrderBy
        If Me.txtKeywords > "" Then
            filterSQL = filterSQL & " WHERE " & Me.sfrmSearch.Form.Filter & " AND " & "(" & BuildFIlter & ")" & " ORDER BY " & Me.sfrmSearch.Form.OrderBy
        End If
    x = 1
    End If 

    If Me.sfrmSearch.Form.Filter <> vbNullString And Me.sfrmSearch.Form.FilterOn = True And Me.sfrmSearch.Form.OrderByOn = False Then
        filterSQL = filterSQL & " WHERE " & Me.sfrmSearch.Form.Filter
            If Me.txtKeywords > "" Then
              filterSQL = filterSQL & " AND " & "(" & BuildFIlter & ")"
            End If
        x = 1
        MsgBox (filterSQL)
    End If 

    If Me.sfrmSearch.Form.OrderBy <> vbNullString And Me.sfrmSearch.Form.OrderByOn = True And Me.sfrmSearch.Form.FilterOn = False Then
        If Me.txtKeywords > "" Then
            filterSQL = filterSQL & " WHERE " & BuildFIlter & " Order By " & Me.sfrmSearch.Form.OrderBy

        Else
            filterSQL = filterSQL & " Order By " & Me.sfrmSearch.Form.OrderBy
        End If
        x = 1
    End If 

    If x = 1 Then
        On Error Resume Next

        'Delete the query if it already exists
        DoCmd.DeleteObject acQuery, "qryExport"

        'Set the query and save it in current database
         Set query = CurrentDb.CreateQueryDef("qryExport", filterSQL)

     Else
       Call btnSearch_Click
    End If


DoCmd.OutputTo acOutputQuery, "qryExport", "Excel Workbook (*.xlsx)", , , , , acExportQualityPrint
 End Sub

i can only set filter in one column all other values are not available and if i set it by one column and export it access ask for parameter value (access puts the subform name as table name and not the queryname as tablename so the columns are not available)

i must set up the recordsource to the sql because i want so see my filtered data drom keywords in my subform

Please try to give a more full description of your problem. What is your setup, what's the exact problem with changing the recordsource, does the form already have a filter (since you're referring to one), etc. Normally, I'd say you could just apply a filter, but with this description I don't have a clue. — Erik A
i have a few of tables in one query. i have a form with the query as source as subform in my mainform, there are no filters set. if i use my keyword search i must set a new recordsource (my sql from search button) .. in my results i cant set up the filters. if i dont click the search button and i only set the filters type in my keywords an click my button to generate a new query for export my data it works — M.o.
Be specific: a few of tables: provide table structure. One query: provide query SQL, if i use my keyword search: how exactly are you searching. i must set a new recordsource: Why? i cant set up the filters: Why?. Answer all these questions properly in a readable format, and I will try to answer your question. — Erik A

Erik A Erik A · Accepted Answer · 2017-08-23T19:12:56

It seems like you're using a lot of VBA code, and you don't fully comprehend what it's doing. I assume you haven't written it yourself.

A quick fix for your problem would be to replace this line:

varWhere = varWhere & " [test_name] LIKE '*" & Me.txtKeywords & "*' "

With this line:

varWhere = varWhere & Me.txtKeywords

This would have the consequence that you would need to enter a valid WHERE statement in the txtKeywords box (e.g. Column1 LIKE "*searchTerm1*" AND Colum2 LIKE "*searchTerm2*"), but that way you have more control over the exact filter you're using.

On the long term, it's a bad plan to adjust actively maintain a database written by someone else, while not having a good understanding of what her/his intent was when writing something, and what the code is doing. You might want to invest in some VBA courses.

Access search with vba - show results in subform without change RecordSource

1 Answers