Change WHERE clause using VBA based on form control

Question

So, being newish to access and only using VBA in excel up until a few months ago there are quite a few commands I have absolutely 0 idea on how to use/correctly write syntax.

Problem: I have a saved query (qry_ExcelExport) which at the moment is just:

SELECT '*' FROM tbl_Contacts

What I want to do is use VBA to add/change the WHERE clause based on a user form control.

Something like:

If me.txt_Flag = "DP Delegate" then 'WHERE [DP-DEL] = True' (or = -1)
Elseif me.txt_Flag = "DP Sponsor" then 'WHERE [DP-SPON] = True' (or = -1)

And so on. (I understand that the syntax above is 100% incorrect, that's just what I'm hoping to achieve)

Using the power of the internet I managed to come across this code:

    ‘To change the Where clause in a saved query  
    Dim qdf as QueryDef
    Dim db as Database
    Set db = CurrentDB
    Set qdf = db.QueryDefs("YourQueryName")
    qdf.SQL = ReplaceWhereClause(qdf.SQL, strYourNewWhereClause)
    set qdf = Nothing
    set db = Nothing

    Public Function ReplaceWhereClause(strSQL As Variant, strNewWHERE As Variant)
    On Error GoTo Error_Handler

    ‘This subroutine accepts a valid SQL string and Where clause, and
    ‘returns the same SQL statement with the original Where clause (if any)
    ‘replaced by the passed in Where clause.
    ‘
    ‘INPUT:
    ‘ strSQL valid SQL string to change
    ‘OUTPUT:
    ‘ strNewWHERE New WHERE clause to insert into SQL statement
    ‘
        Dim strSELECT As String, strWhere As String
        Dim strOrderBy As String, strGROUPBY As String, strHAVING As String

        Call ParseSQL(strSQL, strSELECT, strWhere, strOrderBy, _
            strGROUPBY, strHAVING)

        ReplaceWhereClause = strSELECT &""& strNewWHERE &""_
            & strGROUPBY &""& strHAVING &""& strOrderBy

        Exit_Procedure:
            Exit Function

        Error_Handler:
            MsgBox (Err.Number & ": " & Err.Description)
            Resume Exit_Procedure

    End Function

And that first line... that very first line "To change the Where clause in a saved query" indicates that this is EXACLY what I need.

But, there is no walk-through or step-by-step beginners guide to understanding this code, the syntax or more importantly how to tie it in with a form control and it is not one I've ever used or heard of before.

EDIT: The saved query qry_ExcelExport is used in a funtion to export data

Call exportTable("qry_ExportExcel")

Where I'm calling

Public Sub exportTable(tName As String)
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tName, saveFileAs, True
End Sub

I need the ability to modify the where so that when I export it includes that clause as at the moment there is no WHERE clause so exports just take all the data.

I don't think you need such a complex code if you only need to change the WHERE part with VBA. Save the value of your control into a string variable and then just execute a SQL query with that new string. Something like MySQL="SELECT * FROM tbl_Contacts WHERE " & myWherePart should kind of work. Besides, what does Call ParseSQL? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Apr 26, 2019 at 9:12
That code builds an SQL statement but does nothing with it. What are you really trying to accomplish? If you just want to filter report or form, review allenbrowne.com/ser-62.html — June7
– June7, Commented Apr 26, 2019 at 9:13
I just found that on one of the Microsoft document sites (You know the ones with a lot of technical info that doesn't really help explain usage) I was hoping it would allow me to change "WHERE field1 = true" to "WHERE field2 = True" Edit: I need to change the WHERE clause in saved query as I use that query for calling an excel export — Castiel
– Castiel, Commented Apr 26, 2019 at 10:08

Andre · Accepted Answer · 2019-04-26 10:45:09Z

3

It is normally neither needed nor practical to modify saved queries for filtering.

What you do instead is apply the filter to the form:

If me.txt_Flag = "DP Delegate" then 
    strFilter = "[DP-DEL] = True"
Elseif me.txt_Flag = "DP Sponsor" then 
    strFilter = "[DP-SPON] = True"
Else
    strFilter = ""
End If

Me.Filter = strFilter
Me.FilterOn = (strFilter <> "")

Or if you need the query for something else, you can apply the filter to the query.

Set rs = DB.OpenRecordset("Select * From MySavedQuery Where " & strFilter)

Edit

If the query is used for export, it is actually one of few situations, where modifying the query is useful.

If the query is as simple as in your question, you can simply set the full SQL:

strSql = "SELECT * FROM tbl_Contacts WHERE " & strFilter
db.QueryDefs("qry_ExportExcel").SQL = strSql
Call exportTable("qry_ExportExcel")

or if the base query is more complex, use two queries: a constant one (qry_ExportExcel_Base) and a dynamic one (qry_ExportExcel)

strSql = "SELECT * FROM qry_ExportExcel_Base WHERE " & strFilter

etc. as above

edited Apr 26, 2019 at 10:45

answered Apr 26, 2019 at 9:11

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Castiel Over a year ago

This looks hopeful, however the saved query (qry_Excel) is used to export data - if I use this method would it apply the filters to the export or would that module just continue to export the unchanged saved query

Castiel Over a year ago

Awesome. I'll go back to the drawing board and get something written up and get back to you with my results but just looking at your solution looks like I can adapt to a victorious outcome

Collectives™ on Stack Overflow

Change WHERE clause using VBA based on form control

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related