Pass multiple list box to query without click any button

[ice051505]

Hi all, I have a multi select list box called "Spc_Slc" and want to pass the selected value as criteria to the field called "Species" in my query. My form is called Catch_Selection, so after I built my list box with the RowSource from my original table SpeciesTB, I created a query with all the information I need. In the Species field, I just enter the criteria as: [forms]![Catch_Selection]![Spc_Slc], however, after I selected from my list box, I opened my query and got nothing. I think I will need to do the VBA code to active my list box, can anyone tell me how to do that?

BTW, I saw that there is some way to do this by click a command button, byt I only need the value pass to my query for the source on my other form. Thanks!

 

[StarGrabber]

Hi ice051505,

if I understand you right, you want to pass only one listbox value to your query. If so, this contradicts to a multi select listbox you are talking about first.

If you want to pass one value, set the multiselect property of your listbox to "none". Then you will see records in your query.

If you want to pass more than one value, then you have to use VBA (and set your multiselect property value to the previous one. "cmdRunQuery" is a button on your form):

Private Sub cmdRunQuery_Click()
On Error GoTo Err_Run

    If Spc_Slc.ItemsSelected.Count = 0 Then
        MsgBox "No items selected.", vbExclamation
        Exit Sub
    End If
    
    Dim strCriteria As String
    Dim varItem As Variant

    ' Build a criteria string
    For Each varItem In Spc_Slc.ItemsSelected
        strCriteria = strCriteria & "', '" & Spc_Slc.ItemData(varItem)
    Next varItem
    
    strCriteria = Right(strCriteria, Len(strCriteria) - 3) & "'"
        
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT SpeciesTB.yourFieldName1, SpeciesTB.Species FROM SpeciesTB " & _
        "WHERE SpeciesTB.Species IN (" & strCriteria & ");")

    ' (Do something with the resulting records)
    
    rs.Close
    
Exit_Run:
    Set rs = Nothing
    Exit Sub
    
Err_Run:
    MsgBox Err.Description
    Resume Exit_Run
    
End Sub

 

[ice051505]

Hi,Thanks! But my list box is multiple select which I set the property to simple already, but I don't want to click any button after I select from the list box. I need to pass it to a query criteria without click any button on my form, do you know how to connect my selection with the query? I will use the query as the data souce for my other form!

Hi ice051505,

if I understand you right, you want to pass only one listbox value to your query. If so, this contradicts to a multi select listbox you are talking about first.

If you want to pass one value, set the multiselect property of your listbox to "none". Then you will see records in your query.

If you want to pass more than one value, then you have to use VBA (and set your multiselect property value to the previous one. "cmdRunQuery" is a button on your form):

Private Sub cmdRunQuery_Click()
On Error GoTo Err_Run
 
    If Spc_Slc.ItemsSelected.Count = 0 Then
        MsgBox "No items selected.", vbExclamation
        Exit Sub
    End If
 
    Dim strCriteria As String
    Dim varItem As Variant
 
    ' Build a criteria string
    For Each varItem In Spc_Slc.ItemsSelected
        strCriteria = strCriteria & "', '" & Spc_Slc.ItemData(varItem)
    Next varItem
 
    strCriteria = Right(strCriteria, Len(strCriteria) - 3) & "'"
 
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT SpeciesTB.yourFieldName1, SpeciesTB.Species FROM SpeciesTB " & _
        "WHERE SpeciesTB.Species IN (" & strCriteria & ");")
 
    ' (Do something with the resulting records)
 
    rs.Close
 
Exit_Run:
    Set rs = Nothing
    Exit Sub
 
Err_Run:
    MsgBox Err.Description
    Resume Exit_Run
 
End Sub

 

[StarGrabber]

Well, and this "other form", how do you open it? I guess by clicking a button, right?

I so, you have to change the code a bit. Code of "Catch_Selection":

Private Sub btnOpenOtherForm_Click()

    Dim strCriteria As String
    strCriteria = GetCriteria
    
    DoCmd.OpenForm "OtherForm", OpenArgs:=strCriteria
    
End Sub

Private Function GetCriteria() As String
On Error GoTo Err_Get

    GetCriteria = vbNullString
    
    If Spc_Slc.ItemsSelected.Count = 0 Then
        MsgBox "No items selected.", vbExclamation
        Exit Function
    End If
    
    Dim strCriteria As String
    Dim varItem As Variant

    For Each varItem In Spc_Slc.ItemsSelected
        strCriteria = strCriteria & "', '" & Spc_Slc.ItemData(varItem)
    Next varItem
    
    strCriteria = Right(strCriteria, Len(strCriteria) - 3) & "'"
    
    GetCriteria = strCriteria
    
Exit_Get:
    Exit Function
    
Err_Get:
    MsgBox Err.Description
    Resume Exit_Get
    
End Function

Code of your "other form":

Private Sub Form_Load()
On Error GoTo Err_Load
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT SpeciesTB.yourFieldName1, SpeciesTB.Species FROM SpeciesTB " & _
        "WHERE SpeciesTB.Species IN (" & Me.OpenArgs & ");")

    Set Me.Recordset = rs

Exit_Load:
    Exit Sub
    
Err_Load:
    MsgBox Err.Description
    Resume Exit_Load
    
End Sub

And please do yourself, me and all other forum visitors a favour: do not quote entire posts.

 

[ice051505]

Hi, Sorry for the confusion, I shouldn't mention the other form...haha

Well, the layer of my form is:
-----------------------------------------------------------------------
Choose year range (enter the year)
Choose species (select from Mutiple list box)
Choose Area Zone (Zone1, Zone2, Zone3)

'all the selection will make a query called "SelectionSum" that contains all the data which the user want
After the data selection, I will ask the user to choose more specific detail base on the data which have already been selected

Choose Smaller Area Level
Choose the Sub Area
-----------------------------------------------------------------------
'All the selections are in the same form, and after user selected all the informations, then there will be a button to led them to the next form, to selection the table format they wish to display on the final report.

My problem is on the multiple list box which has the selection of species pass to my first selection query "SelectionSum", Can you show my how to modify the code? and Thank you for your advice on the quote

Well, and this "other form", how do you open it?

 

[StarGrabber]

Maybe I can show you, but what do you exactly want the code to do? In the lines I gave you, you can see how the selection of species is passed to your query (--> recordset).

 

[ice051505]

yes! I saw that, but that is the part that I'm not so clear... how did the sql pass to my query "SelectionSum" which contains the selection of year and area zone?

I need the data selection from these three field (year, species and AreaZone), year is input into the form and AreaZone has three choice, which I put the switch criteria on my query, but how can I put the species selection with other two field together on "SelectionSum" Query?

Maybe I can show you, but what do you exactly want the code to do? In the lines I gave you, you can see how the selection of species is passed to your query (--> recordset).

 

[StarGrabber]

Symply by adding some more parameters:

"SELECT SpeciesTB.Species FROM tblSpecies
WHERE SpeciesTB.Species IN (" & strCriteria & ") AND Year(yourDateField)=" & yourYearCriterion & " AND SpeciesTB.yourAreaZoneField=" & yourZoneCriterion & ";"

 

[ice051505]

Thank you! I get what you mean, just want to be clear that the SpeciesTB in your code is represent the table where I built my list box or the final query that all the three field would be in?

Symply by adding some more parameters:

"SELECT SpeciesTB.Species FROM tblSpecies
WHERE SpeciesTB.Species IN (" & strCriteria & ") AND Year(yourDateField)=" & yourYearCriterion & " AND SpeciesTB.yourAreaZoneField=" & yourZoneCriterion & ";"

 

[StarGrabber]

At this point I think you should have a look at the attached file.

 

[ice051505]

Thank you so much! It finally worked

At this point I think you should have a look at the attached file.

 

[StarGrabber]

Alright then - good luck with your project!