Selecting mutiple choices from either a list box or combo box? 11

[Nate1749]

I have a non-normalized .csv file that we get from an external source. It's then linked to an access table. I think have queries that do a Group By to pull out unique instances for certain columns and then that populates the combo/list box's on a form. The selections made on the form is the criteria for a report that is ran. Currently everything works fine, except now I'm trying to figure out how the user can select multiple criteria.

For instance, in cboLocation there is Online, DuPage, Kane.
Currently I can only select one choise and run the report; I'm trying to figure out how I could possibly select two or all three. I changed the combo box to a list box and tried holding ctrl, but no luck.

-Nate

 

[Mecha]

Thanks Jeff, you code worked, I wont put it up here cause it is the same as the one Nate published.

Thanks idd for all your help too.

Carly

 

[Nate1749]

I meant Jeff!!! Sorry about that Jeff. Thanks for your help too idd.

Mecha, did you get multiple list boxes working? My form (which feeds the report) has 7 of them, Jeff sent me code on how to do it, but it's giving me problems. Thanks,

-Nate

 

[Mecha]

No i am still having problems with the many boxes. i have about 7 as well.

Still having a go at it myself, but i have a feeling i have no idea

Tell me if you have any luck

Carly

 

[bizzaam]

Hello, I'm new to this thread and tek-tips in general. I was searching for code help when I came across this thread. I am having similar problems as Nate and Mecha above. I have a form with 5 fields. 3 of the fields filter each other. For example, in the first Combo Box I choose a process, then the next Combo Box limits itself to locations containing that process, and the next Combo Box limits itself to machines in that location with that process. The other two fields specify the start and end dates. Ideally, I want to create a report using the selected fields. This works fine, except I am limited to selecting 1 machine in the 3rd field I described. I cant figure out how to choose multiple machines in this last box. I know I will have to change it to a list box, but am not sure of the code I have to use. I am not too experienced with programming, and am looking for some help with this. Any ideas? Suggestions? Thanks

Sachin

 

[RamziSaab]

nate are these fields from a table...

if so can u tell me table name and the name of the last combobox

 

[Nate1749]

This is non-working code for the 7 list boxes I have. I figured I would post it and hope someone else tries to poke around at it.

I have tried eliminating part 2 and keeping part 3. I have also tried eliminating all of part 3 and keeping part 2, also tried keeping them both (what's currently below), but it's not working.

You'll see I only labeled Part 1 & 2 on the first two listboxes, the pattern is pretty easy to catch onto.



Private Sub btnRPT74_Click()
On Error GoTo Err_btnRPT74_Click


Dim RepTo As String
Dim ctl As Control
Dim varRow As Variant
Dim strlbCampus As String
Dim strlbDegree As String
Dim strlbMajor As String
Dim strlbEmployerName As String
Dim strlbGradMonth As String
Dim strlbGradYear As String
Dim strlbPlaceStatus As String

Dim strWhere As String


RepTo = "rpt74"
strWhere = ""
strlbCampus = ""
strlbDegree = ""
strlbMajor = ""
strlbEmployerName = ""
strlbGradMonth = ""
strlbGradYear = ""
strlbPlaceStatus = ""


// Part 1
Set ctl = Me!lbCampus

For Each varRow In ctl.ItemsSelected
If strlbCampus = "" Then
strlbCampus = "[Student Campus] in (" & ctl.Column(0, varRow)
Else
strlbCampus = strlbCampus & ", " & ctl.Column(0, varRow)
End If
Next varRow

//Part 2
If strlbCampus <> &quot;&quot; Then
strlbCampus = strlbCampus & &quot&quot;
End If


// Part 1
Set ctl = Me!lbDegree

For Each varRow In ctl.ItemsSelected
If strlbDegree = &quot;&quot; Then
strlbDegree = &quot;[Student Current Degree] in (&quot; & ctl.Column(0, varRow)
Else
strlbDegree = strlbDegree & &quot;, &quot; & ctl.Column(0, varRow)
End If
Next varRow

//Part 2
If strlbDegree <> &quot;&quot; Then
strlbDegree = strlbDegree & &quot&quot;
End If



Set ctl = Me!lbMajor

For Each varRow In ctl.ItemsSelected
If strlbMajor = &quot;&quot; Then
strlbMajor = &quot;[Student Current Major] in (&quot; & ctl.Column(0, varRow)
Else
strlbMajor = strlbMajor & &quot;, &quot; & ctl.Column(0, varRow)
End If
Next varRow


If strlbMajor <> &quot;&quot; Then
strlbMajor = strlbMajor & &quot&quot;
End If



Set ctl = Me!lbEmployerName

For Each varRow In ctl.ItemsSelected
If strlbEmployerName = &quot;&quot; Then
strlbEmployerName = &quot;[Employer Employer Name] in (&quot; & ctl.Column(0, varRow)
Else
strlbEmployerName = strlbEmployerName & &quot;, &quot; & ctl.Column(0, varRow)
End If
Next varRow


If strlbEmployerName <> &quot;&quot; Then
strlbEmployerName = strlbEmployerName & &quot&quot;
End If


Set ctl = Me!lbGradMonth

For Each varRow In ctl.ItemsSelected
If strlbGradMonth = &quot;&quot; Then
strlbGradMonth = &quot;[Student Graduation Month] in (&quot; & ctl.Column(0, varRow)
Else
strlbGradMonth = strlbGradMonth & &quot;, &quot; & ctl.Column(0, varRow)
End If
Next varRow


If strlbGradMonth <> &quot;&quot; Then
strlbGradMonth = strlbGradMonth & &quot&quot;
End If


Set ctl = Me!lbGradYear

For Each varRow In ctl.ItemsSelected
If strlbGradYear = &quot;&quot; Then
strlbGradYear = &quot;[Student Graduation Year] in (&quot; & ctl.Column(0, varRow)
Else
strlbGradYear = strlbGradYear & &quot;, &quot; & ctl.Column(0, varRow)
End If
Next varRow


If strlbGradYear <> &quot;&quot; Then
strlbGradYear = strlbGradYear & &quot&quot;
End If




Set ctl = Me!lbPlaceStatus

For Each varRow In ctl.ItemsSelected
If strlbPlaceStatus = &quot;&quot; Then
strlbPlaceStatus = &quot;[Placement Placement Status] in (&quot; & ctl.Column(0, varRow)
Else
strlbPlaceStatus = strlbPlaceStatus & &quot;, &quot; & ctl.Column(0, varRow)
End If
Next varRow


If strlbPlaceStatus <> &quot;&quot; Then
strlbPlaceStatus = strlbPlaceStatus & &quot&quot;
End If




//Part 3

If strlbCampus <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbCampus
Else
strWhere = strWhere & &quot; And &quot; & strlbCampus
End If
End If


If strlbDegree <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbDegree
Else
strWhere = strWhere & &quot; And &quot; & strlbDegree
End If
End If


If strlbMajor <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbMajor
Else
strWhere = strWhere & &quot; And &quot; & strlbMajor
End If
End If


If strlbEmployerName <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbEmployerName
Else
strWhere = strWhere & &quot; And &quot; & strlbEmployerName
End If
End If


If strlbGradMonth <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbGradMonth
Else
strWhere = strWhere & &quot; And &quot; & strlbGradMonth
End If
End If


If strlbGradYear <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbGradYear
Else
strWhere = strWhere & &quot; And &quot; & strlbGradYear
End If
End If


If strlbPlaceStatus <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbPlaceStatus
Else
strWhere = strWhere & &quot; And &quot; & strlbPlaceStatus
End If
End If



DoCmd.OpenReport RepTo, acViewPreview, , strWhere

Exit_btnRPT74_Click:
Exit Sub

Err_btnRPT74_Click:
MsgBox Err.Description
Resume Exit_btnRPT74_Click

End Sub

 

[jebry]

Hi Nate!

Had last week off so I have been very busy this week. I should have looked at this again last week, sorry for the delay. Some of your values look like they should be text values. If that is the case then you need to add single quotes like this (example):

For Each varRow In ctl.ItemsSelected
If strlbCampus = &quot;&quot; Then
strlbCampus = &quot;[Student Campus] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbCampus = strlbCampus & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow

Let me know if that helps.



Jeff Bridgham
bridgham@purdue.edu

 

[Nate1749]

Jeff, you beat me to it!!! I just found this out and was very happy. I also had vacation all last week. Your help has been greatly appriciated.

I figured it out by looking at the 1st solution you gave me (one listbox) vs. the newest one (multiple listboxes). For anyone who cares, this is my code for 7 list boxes all working. Thanks again Jeff!



Private Sub btnRPT74_Click()
On Error GoTo Err_btnRPT74_Click


Dim RepTo As String
Dim ctl As Control
Dim varRow As Variant
Dim strlbCampus As String
Dim strlbDegree As String
Dim strlbMajor As String
Dim strlbEmployerName As String
Dim strlbGradMonth As String
Dim strlbGradYear As String
Dim strlbPlaceStatus As String

Dim strWhere As String


RepTo = &quot;rpt74&quot;
strWhere = &quot;&quot;
strlbCampus = &quot;&quot;
strlbDegree = &quot;&quot;
strlbMajor = &quot;&quot;
strlbEmployerName = &quot;&quot;
strlbGradMonth = &quot;&quot;
strlbGradYear = &quot;&quot;
strlbPlaceStatus = &quot;&quot;

Set ctl = Me!lbCampus

For Each varRow In ctl.ItemsSelected
If strlbCampus = &quot;&quot; Then
strlbCampus = &quot;[Student Campus] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbCampus = strlbCampus & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbCampus <> &quot;&quot; Then
strlbCampus = strlbCampus & &quot&quot;
End If



Set ctl = Me!lbDegree

For Each varRow In ctl.ItemsSelected
If strlbDegree = &quot;&quot; Then
strlbDegree = &quot;[Student Current Degree] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbDegree = strlbDegree & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbDegree <> &quot;&quot; Then
strlbDegree = strlbDegree & &quot&quot;
End If




Set ctl = Me!lbMajor

For Each varRow In ctl.ItemsSelected
If strlbMajor = &quot;&quot; Then
strlbMajor = &quot;[Student Current Major] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbMajor = strlbMajor & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbMajor <> &quot;&quot; Then
strlbMajor = strlbMajor & &quot&quot;
End If


Set ctl = Me!lbEmployerName

For Each varRow In ctl.ItemsSelected
If strlbEmployerName = &quot;&quot; Then
strlbEmployerName = &quot;[Employer Employer Name] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbEmployerName = strlbEmployerName & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbEmployerName <> &quot;&quot; Then
strlbEmployerName = strlbEmployerName & &quot&quot;
End If


Set ctl = Me!lbGradMonth

For Each varRow In ctl.ItemsSelected
If strlbGradMonth = &quot;&quot; Then
strlbGradMonth = &quot;[Student Graduation Month] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbGradMonth = strlbGradMonth & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbGradMonth <> &quot;&quot; Then
strlbGradMonth = strlbGradMonth & &quot&quot;
End If


Set ctl = Me!lbGradYear

For Each varRow In ctl.ItemsSelected
If strlbGradYear = &quot;&quot; Then
strlbGradYear = &quot;[Student Graduation Year] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbGradYear = strlbGradYear & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbGradYear <> &quot;&quot; Then
strlbGradYear = strlbGradYear & &quot&quot;
End If




Set ctl = Me!lbPlaceStatus

For Each varRow In ctl.ItemsSelected
If strlbPlaceStatus = &quot;&quot; Then
strlbPlaceStatus = &quot;[Placement Placement Status] in ('&quot; & ctl.Column(0, varRow) & &quot;'&quot;
Else
strlbPlaceStatus = strlbPlaceStatus & &quot;, '&quot; & ctl.Column(0, varRow) & &quot;'&quot;
End If
Next varRow


If strlbPlaceStatus <> &quot;&quot; Then
strlbPlaceStatus = strlbPlaceStatus & &quot&quot;
End If










If strlbCampus <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbCampus
Else
strWhere = strWhere & &quot; And &quot; & strlbCampus
End If
End If






If strlbDegree <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbDegree
Else
strWhere = strWhere & &quot; And &quot; & strlbDegree
End If
End If


If strlbMajor <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbMajor
Else
strWhere = strWhere & &quot; And &quot; & strlbMajor
End If
End If



If strlbEmployerName <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbEmployerName
Else
strWhere = strWhere & &quot; And &quot; & strlbEmployerName
End If
End If



If strlbGradMonth <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbGradMonth
Else
strWhere = strWhere & &quot; And &quot; & strlbGradMonth
End If
End If




If strlbGradYear <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbGradYear
Else
strWhere = strWhere & &quot; And &quot; & strlbGradYear
End If
End If




If strlbPlaceStatus <> &quot;&quot; Then
If strWhere = &quot;&quot; Then
strWhere = strlbPlaceStatus
Else
strWhere = strWhere & &quot; And &quot; & strlbPlaceStatus
End If
End If






DoCmd.OpenReport RepTo, acViewPreview, , strWhere

Exit_btnRPT74_Click:
Exit Sub

Err_btnRPT74_Click:
MsgBox Err.Description
Resume Exit_btnRPT74_Click

End Sub

 

[Nate1749]

Everything works great with this setup, however, I am having a small problem with the loading time. It is taking sometimes 2 minutes to load/save a form. What's strange is if I quit out of access and open it right back up, the load time will be minimal (3-5 seconds), but after a save or two it skyrockets to taking 2 minutes (literally). My computer isn't old (p4 1.8ghz, 512megs on windows 2000), and I had posted this problem awhile ago in another thread, but received no replies. This is the other thread I posted it in thread702-560011

 

[Nate1749]

if a blank is selected from the query box, it's not pulling any data (even though there are records w/ blanks). I think it's not feeding how a blank should be handled correctly into the query criteria, any ideas?

IE.. The list box for placement status is..
<-this is a blank
e1s
e1d

If someone selects e1d, it pulls all of them; works fine, however, if someone selects the blank (meaning there isn't anything typed in that column for those records), then it's not pulling anything (even though there should be).

 

[jdegeorge]

Nate1749

I'm having the same problem as you, I think. I have about 5 multi-select list boxes on an unbound form that should allow a user to select one or more values from one or more of the list boxes. If a list box has no values, then it shouldn't be added to the filter.

Any way, this works when the user selects something from all boxes but doesn't work if one or more list boxes have no selected values. Were you able to get this to work for you?

I'm desperate and need a way to let my users select their own report filters from 5 fields. Am I going about this the wrong way?

Jim DeGeorge

 

[RamziSaab]

I am a bit confused, my for allows the user to select the field and then a list box will appear populated by that field

if the no field is selected in the combobox then the macro will skip it by a simple if statement?

e.g.

if me.cbofieldlist.value = vbnullstring then
else
my code
end if

 

[jdegeorge]

Nate1749 and IDD:

I used a combination of your code and code from others to solve my problem. Enjoy the stars!

Jim DeGeorge

 

[idd]

JDEGeorge,

If you don't mind can you post the code in this thread so that others may benefit from it too.

Thanks for the star.

Idd

 

[FancyPrairie]

JDEGeorge put the code in a thread I responded to. However, I modified it somewhat so that it can be a routine that you can call from any report criteria form you create.

Actually, there is a better way of handling this so that the function will not only build the Where clause for listboxes, but also for text boxes, date ranges, etc. But that gets a little more complicated. And that was not the question people asked in this thread or the other.

Note that the routine assumes that the Tag property of the listbox(es) is formatted as Where=TableName.FieldName,DataType,. Where DataType is either the word String or Number. That way the function knows how to format the SQL string.

Also note that the function uses the Bound column of the list box as the value to be included in the Where clause.

Assuming the function encounters no errors, you could call it by passing the form object. Something like this:

Docmd.OpenReport &quot;rptName&quot;,intView,,BuildWhereClause(Me)

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 > 0) 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

 

[jdegeorge]

IDD

Sorry about that. I know I should have posted the solution but was so happy it was working that it slipped my mind.

What FancyPrairie posted isn't what I'm using, but rather his streamlined re-write of my code, which I'm trying to figure out how to adapt.

Jim DeGeorge

 

[Nate1749]

No I never did resolve it. I know it's fairly simple to because it's not feeding a blank into the query criteria correctly.

All of the data fields should have something in it anyways (at least in my database), so I thought it would be neat to select the (blank) field and then have it query out all of those records so we go back and correct that record.

 

[jdegeorge]

Nate

FancyPrairie's generic code did the job for me. Check it out! Oh, and enhoy the star FP!

Jim DeGeorge

 

[Nate1749]

I'm still using the initial code by jebry, except now I am having the problem if there is an apostrophe in the criteria.

For example : Kim's Travel is making the code put the ' before it's over so I'm getting a syntax error. I tried a number of ways of adding or changing the punctuation in the code (double quotes, single quotes)... Is there a way around this, or I may be forced to switch to the code fancyprairie posted.

-Nate

 

[jdegeorge]

Check out a thread that I posted. thread705-653379 came to the rescue. His code works perfectly to solve the &quot;single quote in the text&quot; issue! Good luck.

Jim DeGeorge