multi select list box pass value to query criteria without clicking command 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!

[June7]

You allow multiple selection of rows as criteria? This will require VBA code to construct criteria string http://allenbrowne.com/ser-50.html

[orange]

I agree with June7 you need vba code. But you don't necessarily have to click a button. I think you could do it in the
"after update" or perhaps a lost focus event. But vba code is needed.

[ice051505]

my code is under "after update" event, do you know how to do the code instead of open a report but only use the selection values to pass into a query, I don't need to open the query, it will be the data source for my other form. Thanks!

You allow multiple selection of rows as criteria? This will require VBA code to construct criteria string http://allenbrowne.com/ser-50.html

[orange]

Show us the SQL for your query.
Tell us more about

the selection values to pass into a query

.

You could also describe what the query is for and how the selection values are to be used.

[ice051505]

Thanks for the reply.

I have three main selections on my form, which are year, species and Zone. After the user select from each field, I would like to generate a query from all the selections. Then I want to ask if the user need to select smaller area under the selection zone, so I put a "on click" method on Zone selection to ask for the small area. But before the user select any smaller area, I want my query contains the data selection only from year, species and zone, and I don't want to click any additional commond button to make my "species" selection (the multiple selection list box) work. So far, my query generate perfect without my species selection, and I think I have really trouble to make my query get the information from the multiple selection box.

[ice051505]

This is the layout of my form, hope can make you understand better my problem, Thanks!

Show us the SQL for your query.
Tell us more about .

You could also describe what the query is for and how the selection values are to be used.

[orange]

So far, my query generate perfect without my species selection, and I think I have really trouble to make my query get the information from the multiple selection box.

Please post the sql for your query.

[orange]

It appears that you should be doing some sample vba coding to get some experience.
Here is a link showing some tutorials using/integrating vba and sql.

http://www.fontstuff.com/access/acctut15.htm

Can you post a jpg of your tables and relationships? It may help readers understand your database.

Good luck.

[ice051505]

Thanks for all the reply

Here is the SQL of my query:
SELECT weeks.Year, week_catch.Week, week_catch.Catch, Area.Zone, Area.TotalArea, Area.SmallArea, Area.SmallerArea, Area.SmallestArea, Area.AreaCode, Species.SpeciesRef, Species.Species
FROM weeks INNER JOIN (Species INNER JOIN (Gear INNER JOIN ((Fishery_Type INNER JOIN (((Group_table INNER JOIN (Area INNER JOIN aggregation_scenarios ON Area.AreaCode = aggregation_scenarios.Area) ON Group_table.GroupRef = aggregation_scenarios.Group) INNER JOIN FNName ON Group_table.GroupName = FNName.Name) INNER JOIN week_catch ON aggregation_scenarios.ref = week_catch.Ref) ON Fishery_Type.TypeRef = aggregation_scenarios.Type) INNER JOIN FNID ON FNName.FNID = FNID.FNID) ON Gear.GearRef = aggregation_scenarios.Gear) ON Species.SpeciesRef = week_catch.Species) ON weeks.WeekRef = week_catch.Week
WHERE (((weeks.Year) Between [forms]![Catch_Selection]![BegnYr] And [forms]![Catch_Selection]![EndYr]) AND ((week_catch.Catch)>0) AND ((Area.Zone)=Switch([forms]![Catch_Selection]![Zone_slc]=1,"LFA",[forms]![Catch_Selection]![Zone_slc]=2,"BCI",[forms]![Catch_Selection]![Zone_slc]=3,"LFA") Or (Area.Zone) Like IIf([forms]![Catch_Selection]![Zone_slc]=3,"BCI")) AND ((Species.Species)=[forms]![Catch_Selection]![Spc_Slc]));

[June7]

Use of LIKE operator without wildcard is meaningless. Just use = sign. This IIf is missing the value if condition is false
IIf([forms]![Catch_Selection]![Zone_slc]=3,"BCI",??)

[orange]

Can you tell us in plain English what your query is attempting to do? You seem to have a lot of criteria, and it isn't easy for the reader to follow.
As June7 points out you have a syntax error in your IIF.
A quick reference for Access functions (syntax and examples) is

http://www.techonthenet.com/access/f...ndex_alpha.php

[ice051505]

Thank you guys! I already got what I want, Thanks for all the help!

[orange]

Would you like to post your solution in case others have a similar issue?

[ice051505]

Ok, here is my solution, the code is to pass the multiple list box "Spc_Slc" to my query "Selection2", hope this would help others!

Private Sub Spc_Slc_AfterUpdate()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim lngLen As Long
Dim strDelim As String
Dim strWhere As String

strDelim = """"

Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("Selection2")

With Me.Spc_Slc
 For Each varItem In .ItemsSelected
    If Not IsNull(varItem) Then
    strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
    strCriteria = strCriteria & """" & .Column(1, varItem) & """, "
    Else
     MsgBox "please select at least one species.", vbExclamation
    End If
 Next varItem
End With

lngLen = Len(strWhere) - 1
If lngLen > 0 Then
   strWhere = "Species IN (" & Left$(strWhere, lngLen) & ")"
   lngLen = Len(strCriteria) - 2
   If lngLen > 0 Then
    strSQL = "SELECT Species, SpeciesRef FROM Species " & _
"WHERE Species.Species IN (" & strCriteria & ");"
   End If
End If

qdf.SQL = strSQL

End Sub