Solved

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

Posted on 2011-02-16
8
225 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now