Multiple List box to a query 8

[Crystalguru]

I know this topic is out here a hundred times and trust me I have printed them ALL. But I just can't get it to work. If someone could please help!

This is what I have:
Names of objects are in ()

A form(FindPerson) with a list box(LST) of Software Skills(sft_software). The properties on the list box are set to Multi Select = Simple. This works fine. I can choose multiple software skills.

I would like to choose multiple software skills, click a button, run a query to find Candidates(Query-FindCandidates) that have those skills. I know I need code for this.

Since I am new to VB. Step by step would be great!

Any help is appreciated.

 

[FancyPrairie]

In my previous post the following line of code could cause some problems depending upon whether or not your table name and/or field name contain spaces. Therefore, the brackets could cause problems and you may need to handle them or the lack of them

strFilter = strFilter & strAnd & "[" & strField & "]" & " In ("

 

[FancyPrairie]

I created the code based on what you posted. After looking at my code in my library I realized I forgot that I needed to consider the bound column of the list box. Therefore, the function should look like this:

Function BuildWhereClause(frm As Form) As String

'********************************
'*  Declaration Specifications  *
'********************************

    Dim ctl As Control
    
    Dim varItem As Variant
    
    Dim strAnd As String
    Dim strField As String
    Dim strFilter As String
    Dim strType As String
    
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    
    On Error GoTo ErrHandler

'****************
'*  Initialize  *
'****************

    strFilter = vbNullString
    strAnd = vbNullString
    
'*********************************************************
'*  Loop thru all controls on form to find list box(es)  *
'*********************************************************

    For Each ctl In frm.Controls
                
        If (ctl.ControlType = acListBox) Then
                
        '*************************************************************************************************
        '*  Should this list box be processed?                                                           *
        '*  If so, then tag property contains the name of the table and field and the type of the field  *
        '*      (Structure of tag property:  Where=TableName.FieldName,DataType,    )                    *
        '*      NOTE that the code assumes the tag property is structured properly                       *
        '*************************************************************************************************
            
            If ((ctl.Enabled) And (Not ctl.Locked) And (ctl.ItemsSelected.Count > 1) And (InStr(ctl.Tag, "Where=") > 0)) Then
                
                j = InStr(ctl.Tag, "Where=")
                k = InStr(j, ctl.Tag, ",")
                strField = Mid(ctl.Tag, j + 6, k - (j + 6))
                
                j = InStr(k + 1, ctl.Tag, ",")
                strType = Mid(ctl.Tag, k + 1, j - k - 1)
                
                strFilter = strFilter & strAnd & strField & " In ("
                
        '******************************************
        '*  Loop thru items selected in list box  *
        '******************************************
        
                For Each varItem In ctl.ItemsSelected
                    
                    If (strType = "String") Then
                        strFilter = strFilter & "'" & ctl.Column(ctl.BoundColumn - 1, varItem) & "', "
                    ElseIf (strType = "Number") Then
                        strFilter = strFilter & ctl.Column(ctl.BoundColumn - 1, varItem) & ", "
                    End If
                    
                Next varItem
    
                strFilter = Mid(strFilter, 1, Len(strFilter) - 2) & ") "
                strAnd = " AND "
                
            End If
        End If
    
    Next
    
'***********************************
'*  Return Where clause to caller  *
'***********************************

    BuildWhereClause = strFilter
    
'********************
'*  Exit Procedure  *
'********************
        
ExitProcedure:

    Exit Function

'****************************
'*  Error Recovery Section  *
'****************************
        
ErrHandler:
        
    MsgBox Err.Number & vbCrLf & Err.Description, vbExclamation
    
    BuildWhereClause = "!!!ERROR!!!"
    
    Resume ExitProcedure

End Function

 

[FancyPrairie]

I should have just pulled my code out of my library rather than modifing what was posted here. Because I keep making mistakes.

This (ctl.ItemsSelected.Count > 1) should read (ctl.ItemsSelected.Count > 0)

Note also that this function could be modified to also include text boxes, date ranges, etc.

 

[jdegeorge]

FancyPrairie

This is some serious code! It'll work even if there are, let's say 4 list boxes and only 1 has selected values? Please excuse the question, but that's my situation and the resulting string can't have erroneous "," or "(" or &quot". Looking at this quickly, it seems that it'll be okay, but I need to ask before I embark on the adventure of adapting your very flexible code.

Thanks!

Jim DeGeorge

 

[FancyPrairie]

Yes. It doesn't care how many listboxes you have (0 to many). Also note that sometimes a listbox doesn't apply to a given report. So either disable the listbox or lock it. That way, the function will not include that listbox in the where clause.

 

[jdegeorge]

Fancy

The problem with some reports is that there will always be 4 filter options (list boxes), but depending on the user they may not want to use all filters. It's so flexible that I can't lock or disable a list box.

So, I'm assuming that when the ItemsSelected.Count > 0 returns a false the code will move on to the next list box and not include anything related to the unselected list boxes in the where string.

Jim DeGeorge

 

[FancyPrairie]

I got side tracked and hit the submit button to early. But it will be a simple thing to test. Just copy and paste the function in new module and modify your OnClick event as follows.

Private Sub btnPrintReport_Click()

DoCmd.OpenReport "Area/Function", acPreview, , BuildWhereClause(Me)

Exit Sub

...Keep the rest of your code here in case you don't want to use the BuildWhereClause scenerio.


End Sub

 

[jdegeorge]

FancyPrairie

I created a module for the BuildWhereClause function and remarked out my original code for the button with your "DoCmd" statement.

With none of the list boxes having any selections, the whole report prints as expected because there were no filters. Then I tried using the filters, and no matter if I selected values for one or all or any combination thereof for the listboxes, the entire report printed each time. The filters never got applied.

You said ...Keep the rest of your code here in case you don't want to use the BuildWhereClause scenerio. I'm assuming that I don't need my code any longer because your function replaced it. Is "BuildWhereClause(ME)" all that's needed to call your function?

Jim DeGeorge