Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking Forms to Queries (2003)

    OK, the subject is kind of minimizing the question.

    I'd like to build a form that would accept multiple criteria, and then drive a query. For example, I'd like to have a form which contains a place for a user to select several states from which he would like to see customers. I don't know how many states he would select, but I need to capture them, from one to whatever number he wanted, and then feed those into the criteria of a query.

    Can't be a combo box, because that only allows one choice. So I guess first of all I need to know how to design an input area that would accept several choices from the same field.

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Linking Forms to Queries (2003)

    Bob,

    You could use a multiselect list box to do this. The selection(s) from the multiselect list box can be written to temp tables or stored as vairiables in code and then used to drive your form or other objects.

    Search on multiselect listboxes and you should see a lot of results. There are plenty of good examples in the forum.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Star Lounger
    Join Date
    Apr 2003
    Location
    Morgantown, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking Forms to Queries (2003)

    Found the sections on Multiselect list boxes. Thanks.

    Now, I guess I need some help in constructing the sql statement to use the information from the multiselect list box.

    Let's say I have a listbox called States on a form called PickState. If this was a single selection listbox, then I can put the information Forms![PickState]![States] as the criteria of my query to return all the locations for a particular state. If I change the property of the listbox to multiselect and pick two states, the query returns no results. The underlying SQL of the query shows the following:

    SELECT tblRetailer.strRetailerName, tblRetailer.strAddr1, tblRetailer.strAddr2, tblRetailer.strCity, tblRetailer.strRegion, tblRetailer.strPostalCode
    FROM tblRetailer
    WHERE (((tblRetailer.strRegion)=[Forms]![PickState]![States]));

    I know I at least need to change the equal sign in the Where clause to "in". But what else do I need to do to this statement so that it will use all the selected values from the multiselect listbox?

    Thanks for any help provided.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking Forms to Queries (2003)

    You can't use a multi-select list box directly in a query, you have to assemble the criteria (or the entire SQL statement) in VBA code. See <post:=465,456>post 465,456</post:> for an example.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Linking Forms to Queries (2003)

    Bob,

    You are definitely on the right track. In your example, you need to create the "in" string of the sql clause. To do this, you need to complete the following:

    Insert the following code In the after update event of your multi select listbox:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim ctl As ListBox
    Dim strsql As String
    Dim SelectState as string

    'Select Listbox to Work On
    Set ctl = Me!List_PickState 'assuming the listbox name is List_PickState

    'Create Instring of Items Selected
    SelectState = "(" & SelectedItems(ctl) & ")"

    MsgBox SelectState 'Remove when compelte. For testing purposes.


    Also need to add the following function as a module to create the instring:

    Function SelectedItems(ctl As Control) As String
    ' Returns an IN string for selected items in a listbox

    Dim strActivities As String
    Dim varItem As Variant

    strActivities = ""
    If ctl.ItemsSelected.Count > 0 Then
    For Each varItem In ctl.ItemsSelected
    strActivities = strActivities & "'" & ctl.ItemData(varItem) & "',"
    Next varItem
    End If
    SelectedItems = Left$(strActivities, Len(strActivities) - 1)
    ListCount = ctl.ItemsSelected.Count
    End Function


    Once done, you can refer to SelectState for the instring. I also attached an example database that does this. Just open Form1 and give it a try. Note that I added a function as a module.
    Regards,

    Gary
    (It's been a while!)

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Linking Forms to Queries (2003)

    One last item,

    As Hans said, you will need to build the entire sql string in code to do this.

    One other option is to use the MultiSelect List Box to populate temporary tables and then use the temp tables to join to the main table to make the filter work.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Linking Forms to Queries (2003)

    Bob,

    One last item. To make this work with a query linking to an object on your form (like you said you linked to the combo box or single select listbox), you could write the value of the instring to a text box on the form and then in your query, link to the textbox to get the instring as the filter. HTH
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linking Forms to Queries (2003)

    When you open a form using DoCmd.OpenForm etc you have the option of adding a Where clause to the command.
    A where clause has the same effect as adding another condition to the query.

    This provides great flexibility because you can have a form based on a single query or table, but open it with different data sets without having to modify the query each time.

    I attach an example of using a multiselect list box to build a Where clause to show a list of people from one or more states.

    Because the state names are Text fields in my example, they need to be surrounded by double quotes in the where clause. I have used chr(34) to do that job. (I find it easier to read that way.)
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •