• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

Access 2007, SQL, VBA: Modifying a query via combo box

Good morning everyone, I have a query which is exported with the following code from my switchboard (it uses a module to browse for a folder from http://www.mvps.org/access/api/api0001.htm) :

Private Sub cmdExport_Click()

    Dim strFilter As String
    Dim strSaveFileName As String

    On Error Resume Next
    strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")
    strSaveFileName = ahtCommonFileOpenSave(Filter:=strFilter, _
                                            OpenFile:=False, _
                                            DialogTitle:="Please select filename and location to save your file...", _
    DoCmd.TransferText acExportDelim, "Spec1", "Export_EndNote", strSaveFileName, True

End Sub

The underlying SQL structure of the query is simple right now and is:
SELECT Imported.[Reference Type], Imported.Author, Imported.Year, Imported.Title, Imported.[Secondary Author], Imported.[Secondary Title], Imported.[Place Published], Imported.Publisher, Imported.Volume, Imported.[Number of Volumes], Imported.Number, Imported.Pages, Imported.Section, Imported.[Tertiary Author], Imported.[Tertiary Title], Imported.Edition, Imported.Date, Imported.[Type of Work], Imported.[Subsidiary Author], Imported.[Short Title], Imported.[Alternate Title], Imported.[ISBN/ISSN], Imported.[Original Publication], Imported.[Reprint Edition], Imported.[Reviewed Item], Imported.[Custom 1], Imported.[Custom 2], Imported.[Custom 3], Imported.[Custom 4], Imported.[Custom 5], Imported.[Custom 6], Imported.[Accession Number], Imported.[Call Number], Imported.Label, Imported.Abstract, Imported.Notes, Imported.URL, Imported.[Author Address], Imported.Caption
FROM Imported;

I want to have a listbox on my switchboard (lstFilter) filter by the fields [Reprint Edition] which can contain GET, DNG, or UNC three letter codes and/or [Reviewed Item] which contains only the codes INC or EXC.  (I.e. I would like someone to be able to select that they want both GET and EXC or GET and INC, I would also like there to be a function for <All> that applys no filter to the query)  Right now, the code is working well to export the imported table, but I was hoping some SQL and Access experts could help me take it to the next step.  

Thanks in advance, Bevo
  • 5
  • 3
1 Solution
Michael VasilevskySolutions ArchitectCommented:
Use the below function to generate the WHERE clause of your SQL statement based on the selections in the multi-select listbox. Then change the report SQL using this function at runtime:

Function ChangeQDef(Q As String, strSQL As String)
'Changes the SQL of the query Q to strSQL
On Error GoTo Err_ChangeQDef

    Dim qd As QueryDef

    Set qd = CurrentDb.QueryDefs(Q)
    qd.SQL = strSQL

    Exit Function

    MsgBox Err.Description
    Resume Exit_ChangeQDef
End Function
Function FilterGeneric() As String
On Error GoTo Err_FilterGeneric
    Dim frm As Form
    Dim ctl As Control
    Dim varItem As Variant
    Dim strSQL as String, FilterString as String
    Set frm = Forms!MyForm
    Set ctl = frm(MyListBox)

    FilterString = ""
    'enumerate selected items and concatenate to strSQL
    For Each varItem In ctl.ItemsSelected
         If ctl.ItemData(varItem) = 0 Then
              FilterGeneric = ""
              Exit Function
              FilterString = FilterString & " " & rst!FilterSQLWHEREClause & "= " & ctl.ItemData(varItem) & " OR "
         End If
    Next varItem
    If Nz(FilterString, "") = "" Then
          FilterGeneric = ""
          Exit Function
    End If
    'Trim the end of strSQL
    FilterString = Left$(FilterString, InStrRev(FilterString, "OR") - 2)
    FilterGeneric = FilterString
    Exit Function

    MsgBox Err.Description
    Resume Exit_FilterGeneric
End Function

Open in new window

Michael VasilevskySolutions ArchitectCommented:

this line: FilterString = FilterString & " " & rst!FilterSQLWHEREClause & "= " & ctl.ItemData(varItem) & " OR "
needs to be: FilterString = FilterString & " " & [Reprint Edition] & "= " & ctl.ItemData(varItem) & " OR "
BevosAuthor Commented:
I can't seem to get it working mvasilevsky, even with the edit.  Thus far I've copied and pasted your code into the VB portion of my form_switchboard and renamed query Q to Export_EndNote as well as changing the settings for frm and ctrl.  I did however change a few things because I couldn't get the list box to display how I wanted which might be causing this.  I instead put 2 combo boxes on the switchboard cmbFilter1 (for [Reprint Edition] and cmbFilter2 for [Reviewed Item].  I'm attaching my database to this mail in case you can help.  Sorry if this is simple and a bother, but I don't understand how else to fix it.

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Michael VasilevskySolutions ArchitectCommented:
Ok I added a multiselect listbox because I think that's how you wanted it and updated the code. Have a look.

BevosAuthor Commented:
Hi mvasilevsky, I would prefer the combo boxes because they don't take up as much space on the switchboard.  I thought with a listbox I could get it so that (in the same box) you could select the variables from multiple fields (for example GET and then INC from [Reprint Edition] and [Reviewed item] respectively).  Instead, I think the best way to go about this might be 2 combo boxes.  One thing I noticed was that with the list box method you showed, if I select one filter and then change to another it doesn't seem to change the sql query's filter to the second selection.  Do you think the best way around this would be to make a 'remove filter' button?  Also if you could show me how to edit the code to make it work with a combo box rather than list I would appreciate it.  
Thanks so much, Bevo
Michael VasilevskySolutions ArchitectCommented:
ok with comboboxes
BevosAuthor Commented:
This is the best! Thanks mvasilevsky
Michael VasilevskySolutions ArchitectCommented:
Glad to help :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now