Results 1 to 15 of 15
  1. #1
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76

    multi select list box pass value to query criteria without clicking command button

    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!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    You allow multiple selection of rows as criteria? This will require VBA code to construct criteria string http://allenbrowne.com/ser-50.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    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.

  4. #4
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    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!


    Quote Originally Posted by June7 View Post
    You allow multiple selection of rows as criteria? This will require VBA code to construct criteria string http://allenbrowne.com/ser-50.html

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    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.

  6. #6
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    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.

  7. #7
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    This is the layout of my form, hope can make you understand better my problem, Thanks!

    Quote Originally Posted by orange View Post
    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.
    Last edited by ice051505; 02-19-2013 at 01:18 PM.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    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.

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    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.

  10. #10
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    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]));
    Last edited by ice051505; 02-19-2013 at 01:17 PM.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,031
    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"
    ,??)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504
    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

  13. #13
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    Thank you guys! I already got what I want, Thanks for all the help!

  14. #14
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,504

  15. #15
    ice051505 is offline Advanced Beginner
    Windows XP Access 2002
    Join Date
    Feb 2013
    Posts
    76
    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!

    Code:
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-12-2013, 10:41 AM
  2. Replies: 5
    Last Post: 08-02-2012, 09:44 AM
  3. Multi-Select List Box as Criteria in Query
    By broadwat in forum Queries
    Replies: 6
    Last Post: 09-19-2011, 07:47 AM
  4. Replies: 4
    Last Post: 07-27-2011, 09:52 AM
  5. Replies: 1
    Last Post: 01-28-2011, 07:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums