Multi Select Listbox to pass selected Values to a Query

[padlocked17]

All -

For the life of me I can't get the Multiselect Listbox to correctly pass along all of the item selections to a Query which a form is based on.

I've been up and down the forum, and I can't figure out what piece of code to use and how to use it successfully.

I've been able to get a string created using the example posted here and it's in the format of "54,67,89,100" etc.

Public Function

Public Function fMultiSelect(ctlRef As ListBox) As Variant 
    Dim Criteria As String 
    Dim i As Variant 

   ' Build criteria string from selected items in list box. 
    Criteria = "" 
    For Each i In ctlRef.ItemsSelected 
        If Criteria <> "" Then 
            Criteria = Criteria & "," 
        End If 
        Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000") 
    Next i 

fMultiSelect = Criteria 
gMultiSelect = Criteria 
End Function

Call:

Call fMultiSelect(Forms!frmPreSPIPComp!lstProjects)

I now need to pass that string to a Query. Once it's been passed to the query, I can open the report based on it.

Essentially I have a button that will perform the string creation, and I would then like to open a report. I want to base the report off of a query and then have the query use Criteria to dwindle down the report.

Am I missing something here?

The long explanation:

I have a single form that allows for the selection of the report. Once the report is selected, certain fields appear that allow for certain criteria to be selected (ie. Class Name, Multi-Select Class Name, Student Names, Multi select Student Names, Dates, etc.)

Once the report has been selected and the criteria set, a user hits a single button that runs the specific report.

Any better ideas of how to set this up. The reports will ultimately be basing their criteria on what the form has in all of it's unbound fields.

I also have a table that specifies the Display Name, actual Report Name for the button to figure out what report to run.

Bottom Line. I want to use a Listbox to filter a report. If I can use a query to base the report off of even better. I don't want to create the SQL in VBA.

Any ideas? Thanks!

 

[pbaldy]

My preference is usually to leave the query as generic as possible (no criteria). I often do this type of thing:

  strSQL = "Type = '"

  For Each varItem In ctl.ItemsSelected
      strSQL = strSQL & ctl.ItemData(varItem) & "' OR Type = '"
  Next varItem

  strSQL = left$(strSQL, Len(strSQL) - 12)

  DoCmd.OpenReport "rptDriversByType", acViewPreview, , strSQL

 

[padlocked17]

Paul -

Could you shoot me a little more guidance on that. I'm looking and I think I'm just drawing a blank on how to implement.

Thanks Air Force Dad!

 

[pbaldy]

Well, that builds a string like:

Type = 'This' OR Type = 'That'

and passes it in the wherecondition argument of OpenReport, which can be used to restrict records instead of criteria in a query. The wherecondition is basically the same as an SQL WHERE clause without the word WHERE. Clear as mud now?

I'm actually leaving in a few minutes to visit Air Force Daughter, who just returned from a deployment. Air Force Son-in-law is a pilot and is deployed right now.

 

[padlocked17]

Alrighty. Got it doing what I wanted with:

Private Sub cmdOpenReport_Click()
On Error GoTo cmdOpenReport_Click_Err

Dim strSQLMember As String
Dim strSQLClass As String
Dim finalStrSQL As String

'===================================================
'Below we'll get the values for the members listbox
'===================================================
    If Me.lboSelectMember.ItemsSelected.Count = 0 Then
    'Do Nothing
    
    Else
    'MsgBox "It's not Null", vbOKOnly
    'strSQLMember = "tblEnrollment.MemberID = "
    strSQLMember = "MemberID = "

    For Each varItem In Me.lboSelectMember.ItemsSelected
        'strSQLMember = strSQLMember & Me.lboSelectMember.ItemData(varItem) & " OR tblEnrollment.MemberID = "
        strSQLMember = strSQLMember & Me.lboSelectMember.ItemData(varItem) & " OR MemberID = "
    Next varItem
    
    'strSQLMember = Left$(strSQLMember, Len(strSQLMember) - 28)
    strSQLMember = Left$(strSQLMember, Len(strSQLMember) - 14)
    
    'MsgBox "" & strSQLMember & "", vbOKOnly
    End If
'==================================================
'Below we'll get the values for the classes listbox
'==================================================
    If Me.lboSelectClass.ItemsSelected.Count = 0 Then
    'Do Nothing
    
    Else
    'strSQLClass = "tblClasses.ClassID = "
    strSQLClass = "ClassID = "

    For Each varItem In Me.lboSelectClass.ItemsSelected
        'strSQLClass = strSQLClass & Me.lboSelectClass.ItemData(varItem) & " OR tblClasses.ClassID = "
        strSQLClass = strSQLClass & Me.lboSelectClass.ItemData(varItem) & " OR ClassID = "
    Next varItem

    'strSQLClass = Left$(strSQLClass, Len(strSQLClass) - 24)
    strSQLClass = Left$(strSQLClass, Len(strSQLClass) - 13)
    MsgBox "" & strSQLClass & "", vbOKOnly
    End If
    
'==================================================
'Conditions for how to build the SQL string
'==================================================
    
'If Member is Null and Class Is Null
If Me.lboSelectMember.ItemsSelected.Count = 0 And Me.lboSelectClass.ItemsSelected.Count = 0 Then
    'Do Nothing
    finalStrSQL = ""
'If Member is Null and Class Isn't
ElseIf Me.lboSelectMember.ItemsSelected.Count = 0 And Me.lboSelectClass.ItemsSelected.Count > 0 Then
    finalStrSQL = strSQLClass
    
'If Member isn't Null and Class Is
ElseIf Me.lboSelectMember.ItemsSelected.Count > 0 And Me.lboSelectClass.ItemsSelected.Count = 0 Then
    finalStrSQL = strSQLMember

'If Member isn't Null and Class isn't Null
ElseIf Me.lboSelectMember.ItemsSelected.Count > 0 And Me.lboSelectClass.ItemsSelected.Count > 0 Then
    finalStrSQL = strSQLMember & " AND " & strSQLClass

    
MsgBox "" & finalStrSQL & "", vbOKOnly

End If
  DoCmd.OpenReport Me.cboSelectReport, acViewPreview, , finalStrSQL

cmdOpenReport_Click_Exit:
    Exit Sub

cmdOpenReport_Click_Err:
    MsgBox Error$
    Resume cmdOpenReport_Click_Exit

End Sub

My only question is how the heck do I refer to a value of a column in the cboSelectReport combo box? Right now that selection contains a row with the Display Name, actual Report Name, and then what fields the user should be able to sort by. Once the selection is made I need to enable controls on the form, since they are all not enabled on load.

Thanks!

 

[padlocked17]

Doah.

var1 = Me.ComboBox.column(#)