Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Location
    Ohio
    Posts
    3

    Unanswered: Multiselect to populate form

    I need to be able to select multiple records from a multiselect listboxon Form1 then have those records combine to populate Form2.

    *I'm using Northwind Database to test in, CategoryID is a number field

    Form1
    listbox name: "lstProcess"
    multiselect: extended
    Row Source: SELECT Categories.CategoryID, Categories.CategoryName FROM Categories;

    Command Button:
    Private Sub Command3_Click()
    Dim Q As QueryDef, DB As Database
    Dim Criteria As Integer 'if ID field is number
    'Dim Criteria As String 'if ID field is text
    Dim ctl As Control
    Dim Itm As Variant

    ' Build a list of the selections.
    Set ctl = Me![lstProcess]

    For Each Itm In ctl.ItemsSelected
    If Len(Criteria) = 0 Then
    Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
    'if ID field is number
    Criteria = ctl.ItemData(Itm)
    'if ID field is text
    'Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
    Else
    Criteria = Criteria & "," & ctl.ItemData(Itm)
    'if ID field is number
    'Criteria = Criteria & "," & ctl.ItemData(Itm)
    'if ID field is text
    ' Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
    & Chr(34)
    End If
    Next Itm

    If Len(Criteria) = 0 Then
    Itm = MsgBox("You must select one or more items in the" & _
    " list box!", 0, "No Selection Made")
    Exit Sub
    End If

    ' Modify the Query.
    Set DB = CurrentDb()
    Set Q = DB.QueryDefs("qryProcess")
    Q.SQL = "Select * From Categories Where [CategoryID] In(" & Criteria & ");"
    Q.Close

    ' Run the query.
    DoCmd.OpenQuery "qryProcess"
    End Sub

    This code works perfectly if you are using an ID field that is text as in the case with the example (http://support.microsoft.com/kb/135546).

    Any advice is greatly appreciated. Let me know if I've not provided enough information.
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The first error is this:

    Dim Criteria As Integer

    The KB doesn't have this as an integer, and it must be declared as a String. The value you're storing in it is a string no matter what the data types are (because of the commas).

    Secondly, you're handling your value as a string the first pass through and as a number every other time. As yours is a number, drop both Chr(34) from the first section.

    Code:
       For Each Itm In ctl.ItemsSelected
          If Len(Criteria) = 0 Then
             Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
          Else
          Criteria = Criteria & "," & ctl.ItemData(Itm)
          End If
       Next Itm
    Paul

Posting Permissions

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