Solved

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

Posted on 2011-02-16
8
231 Views
Last Modified: 2012-05-11
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
Comment
Question by:Bevos
  • 5
  • 3
8 Comments
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 34909852
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
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 34909868
edit:

this line: FilterString = FilterString & " " & rst!FilterSQLWHEREClause & "= " & ctl.ItemData(varItem) & " OR "
needs to be: FilterString = FilterString & " " & [Reprint Edition] & "= " & ctl.ItemData(varItem) & " OR "
0
 

Author Comment

by:Bevos
ID: 34910365
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 34911665
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
 

Author Comment

by:Bevos
ID: 34917924
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
 
LVL 10

Accepted Solution

by:
Michael Vasilevsky earned 500 total points
ID: 34922488
ok with comboboxes
Example-Rev-Copy-EE2.zip
0
 

Author Comment

by:Bevos
ID: 34926723
This is the best! Thanks mvasilevsky
0
 
LVL 10

Expert Comment

by:Michael Vasilevsky
ID: 34926797
Glad to help :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question