• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • 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...", _
                                            Flags:=ahtOFN_HIDEREADONLY)
    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
0
Bevos
Asked:
Bevos
  • 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_ChangeQDef:
    Exit Function

Err_ChangeQDef:
    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
         Else
              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_FilterGeneric:
    Exit Function

Err_FilterGeneric:
    MsgBox Err.Description
    Resume Exit_FilterGeneric
End Function

Open in new window

0
 
Michael VasilevskySolutions ArchitectCommented:
edit:

this line: FilterString = FilterString & " " & rst!FilterSQLWHEREClause & "= " & ctl.ItemData(varItem) & " OR "
needs to be: FilterString = FilterString & " " & [Reprint Edition] & "= " & ctl.ItemData(varItem) & " OR "
0
 
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.

Thanks,
Bevo
Copy-of-Example-Rev-Copy.accdb
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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.

MV
Copy-of-Example-Rev-Copy-EE.zip
0
 
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
0
 
Michael VasilevskySolutions ArchitectCommented:
ok with comboboxes
Example-Rev-Copy-EE2.zip
0
 
BevosAuthor Commented:
This is the best! Thanks mvasilevsky
0
 
Michael VasilevskySolutions ArchitectCommented:
Glad to help :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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