Create Report Menu - List box Filter? 1

[misscrf]

I have a report and I want to set up a menu so users can filter using a multi select list box. I have seen some sites that show examples of how to do this, but I have never really learned much about list boxes and how to use them. This is my current set up:

I have a report called rptByStatusLocation. It has all of the main record summary information for this application. This has a query called qryRecordByLocationReport.

I set a form to this as well and created a list box on the form called LstRecordStatus.

The rowsource for this is the same field in the query that is in the report for RecordStatus (txtRecordStatus)

This is the code I have on the form:

Private Sub cmdReportByLocation_Click()
On Error GoTo Err_cmdReportByLocation_Click

    Dim stDocName As String

    stDocName = "rptByStatusLocation"
    DoCmd.OpenReport stDocName, acPreview, , GetCriteria()

Exit_cmdReportByLocation_Click:
    Exit Sub

Err_cmdReportByLocation_Click:
    MsgBox Err.Description
    Resume Exit_cmdReportByLocation_Click
    
End Sub

Private Function GetCriteria() As String
   'Multi-Select Listbox
            Dim ctlList
            Set ctlList = Me.LstRecordStatus
            
            If Me.LstRecordStatus.ItemsSelected.Count = 0 Then
                'do nothing
            Else
                strWhere = strWhere & " AND Product IN ("
                
                For Each Lmnt In ctlList.ItemsSelected
                   
                  strWhere = strWhere & "'" & ctlList.ItemData(Lmnt) & "',"
         
                Next
                strWhere = strWhere & ")"
            End If
End Function

When I run the form and choose 1 of the options, but not both, I still get both (only 2 options are there right now, because they are the only ones used in any of the records in the application right now).

I am not sure what I am not doing right, because I am so new to list boxes. Any help is greatly appreciated. Eventually I have 2 more list boxes I want to add to this form so a user can run 3 filters choosing none or all of each of the 3 sets of filters.

Any help is greatly appreciated.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

If you debug your code, you would see the criteria beginning with "AND". Since there is nothing to "AND" to, the expression won't work. You also need to get rid of the final comma.
Try:

Private Sub cmdReportByLocation_Click()
On Error GoTo Err_cmdReportByLocation_Click

    Dim stDocName As String
    Dim strWhrCndtn as String
    strWhrCndtn = "1 = 1 "
    stDocName = "rptByStatusLocation"
    DoCmd.OpenReport stDocName, acPreview, , strWhrCndtn  & _
       GetCriteria()

Exit_cmdReportByLocation_Click:
    Exit Sub

Err_cmdReportByLocation_Click:
    MsgBox Err.Description
    Resume Exit_cmdReportByLocation_Click
    
End Sub

Private Function GetCriteria() As String
   'Multi-Select Listbox
    Dim ctlList as ListBox
    Set ctlList = Me.LstRecordStatus
    If ctlList.ItemsSelected.Count = 0 Then
                'do nothing
       Else
          strWhere = strWhere & " AND Product IN ("
          For Each Lmnt In ctlList.ItemsSelected
              strWhere = strWhere & "'" & ctlList.ItemData(Lmnt) & "',"
          Next
          'remove the final comma
          strWhere = Left(strWhere,Len(strWhere)-1) 
          strWhere = strWhere & ")"
    End If
End Function

I would probably send the listbox control, a field name, and field type into the GetCriteria() function so it could be used for building In ()s for any number of multi-select list boxes.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you for your response and your assistance. I tried to add this, but it still gives me all of the statuses, not limiting the report to the 1 of the 2 that I choose. I think it may have something to do with the line:

 strWhere = strWhere & " AND txtStatus IN ("

This is the field in my query. In my report I actually have a control called status, as I concatenated "Status: "&[txtStatus]

Is this an issue? I am really interested in understanding all of this once I can get this part to work. I am particularly interested in how I can send the listbox control, field name, and type into the GetCriteria() function so I can use it for building In()s for more listboxes. That is exactly what I need to do!!!

Thanks again. I am going to keep working on this. any help you can give would be great.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I realized you mean that I needed to take that AND out of there, but I did that and it still didnt work. I did some more digging to find other options. I found this site: http://support.microsoft.com/kb/135546"]

http://support.microsoft.com/kb/135546

[/URL]

I used method 1 and now it works!!!

here is my final code:

Private Sub cmdReportByVenue_Click()
 Dim stDocName As String
 Dim Criteria As String
 Dim i As Variant
 stDocName = "rptCaseByVenue"

   Criteria = ""
   For Each i In Me![LstCaseStatus].ItemsSelected
      If Criteria <> "" Then
         Criteria = Criteria & " OR "
      End If
      Criteria = Criteria & "[txtcasestatus]='" _
       & Me![LstCaseStatus].ItemData(i) & "'"
   Next i
   
   DoCmd.OpenReport stDocName, acPreview, , Criteria

   Me.Filter = Criteria
   Me.FilterOn = True

End Sub

Now, I have moved on to the "evolved" question, lol. I want to add 2 more mult-select listboxes to be able to allow a person to choose from 1 or all 3 of those, to choose their criteria for this report. Each listbox is for a field in the control source of that report, and it is items like Status, Jurisdiction, and Venue. So they might want all Active(Status) Federal(Jurisidiction) records in the a, b and c venues. Something like that.

How can I set this form up with that nice code above to let me have multiple multi-select list boxes for the report criteria, please? Any help is again greatly appreciated. Hey, at least you know I am serious about figuring this out, and not just fishing for an answer!!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would change back to the IN () functionality especially since you want to add more multi-select list boxes. This is the generic function that I use for all of my m-s list boxes:

Function BuildIn(lboListBox As ListBox, _
        strFieldName As String, strDelim As String) As String
    'send in a list box control object
    'strFieldName is the name of the field in the report's record source
    'strDelim is the delimiter for numbers use "" for text """" and dates "#"
    Dim strIn As String
    Dim varItem As Variant
        
    If lboListBox.ItemsSelected.Count > 0 Then
        strIn = " AND " & strFieldName & " In ("
        For Each varItem In lboListBox.ItemsSelected
            strIn = strIn & strDelim & lboListBox.ItemData(varItem) & strDelim & ", "
        Next
        'remove the last ", " and add the ")"
        strIn = Left(strIn, Len(strIn) - 2) & ") "
    End If
    BuildIn = strIn
End Function

Place the above function in a standard module and call it like:

Private Sub cmdReportByVenue_Click()
 Dim stDocName As String
 Dim strCriteria As String
 stDocName = "rptCaseByVenue"
 strCriteria = "1=1 "
 strCriteria = strCriteria & _ 
   BuildIn(Me.LstCaseStatus,"txtcasestatus","'")
 'add another strCriteria = strCriteria & BuildIn(...)
 'add another strCriteria = strCriteria & BuildIn(...)
 DoCmd.OpenReport stDocName, acPreview, , strCriteria
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I used this and it worked like a charm. Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Ok, so I have moved on to trying the next part of my challenge. I want to allow a user to come to this report menu and either run the report, or just have a subform become visible with the filtered results in a datasheet view (subform).

On open of the form with these listboxes and the button to run the report, I have a subform set up to a grid view of the query that the report is based on. On open of the form, the subform is set to invisible. Now I want to have a button that can be just to run the "summary". It will sit next to the report button. The report button will (of course) run the report, but the summary button, I want that to make the subform visible ( I know how to do that haha) but also set the stcriteria for the query that the subform is bound to. It is the same exact query that the report is bound to.

This is what I put together for the "Summary" button that is meant to make a subform visible with the filtered results:

Private Sub cmdSummary_Click()
 
 Dim Mysql As String
 Dim strCriteria As String
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
   BuildIn(Me.LstCaseStatus, "txtcasestatus", "'")
 strCriteria = strCriteria & _
   BuildIn(Me.lstJurisdiction, "txtJurisdiction", "'")
strCriteria = strCriteria & _
   BuildIn(Me.lstVenue, "txtVenue", "'")
[COLOR=red]   Mysql = "SELECT tblCase.PKCaseID, tblCase.txtCaseName, tblCaseStatus.txtCaseStatus, tblCase.txtDocketNo, tblCase.intMatter, tblJurisdiction.txtJurisdiction, tblVenue.txtVenue, tblCase.txtCaseComments " _
& "FROM tblCase " _
& "(LEFT JOIN tblCaseStatus ON tblCase.FKCaseStatus = tblCaseStatus.PKCaseStatusID) LEFT JOIN (tblJurisdiction RIGHT JOIN tblVenue ON tblJurisdiction.PKJurisdictionID = tblVenue.FKJurisdiction) ON tblCase.FKVenue = tblVenue.PKVenueID where  "
   Mysql = Mysql & strCriteria
   Me![frmSubStatusVenueQry].Form.RecordSource = Mysql
   Me.frmSubStatusVenueQry.Visible = True   [/color]
End Sub

The red is the part I added. I basically took the query that is the control source of that subform (its the same query for the report) and converted its sql to vba sql. I even used the debug.print sql to check the statement out.

When I try to run this, I get a run-time error 3131 "Syntax Error in From Clause" Any thoughts? I am so close to a great report menu for these folks!!!

the debug.print sql gives me this:

SELECT tblCase.PKCaseID, tblCase.txtCaseName, tblCaseStatus.txtCaseStatus, tblCase.txtDocketNo, tblCase.intMatter, tblJurisdiction.txtJurisdiction, tblVenue.txtVenue, tblCase.txtCaseComments FROM tblCase (LEFT JOIN tblCaseStatus ON tblCase.FKCaseStatus = tblCaseStatus.PKCaseStatusID) LEFT JOIN (tblJurisdiction RIGHT JOIN tblVenue ON tblJurisdiction.PKJurisdictionID = tblVenue.FKJurisdiction) ON tblCase.FKVenue = tblVenue.PKVenueID where

Which I think matches the sql of the subforms query control source (which works just fine)

SELECT tblCase.PKCaseID, tblCase.txtCaseName, tblCaseStatus.txtCaseStatus, tblCase.txtDocketNo, tblCase.intMatter, tblJurisdiction.txtJurisdiction, tblVenue.txtVenue, tblCase.txtCaseComments
FROM (tblCase LEFT JOIN tblCaseStatus ON tblCase.FKCaseStatus = tblCaseStatus.PKCaseStatusID) LEFT JOIN (tblJurisdiction RIGHT JOIN tblVenue ON tblJurisdiction.PKJurisdictionID = tblVenue.FKJurisdiction) ON tblCase.FKVenue = tblVenue.PKVenueID;

I am new to converting SQL into VBA statements so it is entirely possible I just botched up the syntax. Any help you can give would be great!!!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Nevermind. I got it working!!!
I found the error in my syntax

SELECT tblCase.PKCaseID, tblCase.txtCaseName, tblCaseStatus.txtCaseStatus, tblCase.txtDocketNo, tblCase.intMatter, tblJurisdiction.txtJurisdiction, tblVenue.txtVenue, tblCase.txtCaseComments [COLOR=red] FROM tblCase (LEFT JOIN [/color] tblCaseStatus ON tblCase.FKCaseStatus = tblCaseStatus.PKCaseStatusID) LEFT JOIN (tblJurisdiction RIGHT JOIN tblVenue ON tblJurisdiction.PKJurisdictionID = tblVenue.FKJurisdiction) ON tblCase.FKVenue = tblVenue.PKVenueID where

needed to be

SELECT tblCase.PKCaseID, tblCase.txtCaseName, tblCaseStatus.txtCaseStatus, tblCase.txtDocketNo, tblCase.intMatter, tblJurisdiction.txtJurisdiction, tblVenue.txtVenue, tblCase.txtCaseComments [COLOR=red] FROM (tblCase LEFT JOIN [/color] tblCaseStatus ON tblCase.FKCaseStatus = tblCaseStatus.PKCaseStatusID) LEFT JOIN (tblJurisdiction RIGHT JOIN tblVenue ON tblJurisdiction.PKJurisdictionID = tblVenue.FKJurisdiction) ON tblCase.FKVenue = tblVenue.PKVenueID where

notice the red parts for the little tiny change that made all the difference!!
__________________


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would try create a saved query with SQL equivalent to the record source of the report. Then use some DAO code to change the SQL property of the saved query and set the Source Object of the subform control.

  CurrentDb.QueryDefs("qselCases").SQL = MySQL
  Me.frmSubStatusVenueQry.SourceObject = "QUERY.qselCases"

If you have an issue, you can check the design view of qselCases.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I do have a saved query set as the default control source for the subform it is qryCaseByVenueReport

would I set it to this:

CurrentDb.QueryDefs("qryCaseByVenueReport").SQL = MySQL
Me.frmSubStatusVenueQry.SourceObject = "QUERY.qryCaseByVenueReport"

not to be dense, but if I do that, how does it change my code in the click to show the subform?

Private Sub cmdSummary_Click()
 
 Dim Mysql As String
 Dim strCriteria As String
 strCriteria = "1=1 "
 strCriteria = strCriteria & _
   BuildIn(Me.LstCaseStatus, "txtcasestatus", "'")
 strCriteria = strCriteria & _
   BuildIn(Me.lstJurisdiction, "txtJurisdiction", "'")
strCriteria = strCriteria & _
   BuildIn(Me.lstVenue, "txtVenue", "'")
  CurrentDb.QueryDefs("qryCaseByVenueReport").SQL = MySQL
Me.frmSubStatusVenueQry.SourceObject = "QUERY.qryCaseByVenueReport"
   Mysql = Mysql & strCriteria
    Me.frmSubStatusVenueQry.Visible = True   
End Sub

is that right?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I don't see where you code builds a full sql statement as MySQL? You need to create a complete SQL statement and assign it to the SQL property of a saved query. Then change the Source Object to the query and set the subform control to visible.


Duane
Hook'D on Access
MS Access MVP