Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Angry Unanswered: Multi select list box query

    I was wondering what I have to do to get the selections in a multi select list box to show up in a query. Just the plain list box shows up just fine with one selection, but when I changed the multi select property to Extended, it wouldn't pull across into the query.

    Any thoughts on this would be greatly appreciated.

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    If I'm using a multi-select list box to run a query I add the selected values to a temporary table and use that for the query.

    I can post some code examples of what I've used if you want.

  3. #3
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    Thanks for the reply.
    That would be great. I don't know much about coding, but I am learning. I don't understand it. The plain list box works fine, but when you make it a multi select listbox, nothing works.

  4. #4
    Join Date
    May 2003
    Posts
    4
    I use the following code to pass selected items from a multi-select list box to an query created on the fly via SQL. I got this code from someone else but have added comments in the code where I know what it does.

    The listbox is called lstRecords and is poluated by the results of a query.

    The SQL does the following. Updates the Authorised field in the Orders table and set it to True where the OrderID matches one selected in the listbox.
    The second does a similar thing for the DateAuthorised field but this time it populates it with the contents of a field from the form.

    You turn the warnings off so that it doesn't prompt you to confirm updating the records, and then you turn them back on again for future use.
    ----------------------------

    On Error GoTo ErrUpdate

    Dim strCriteria As String ' Declare the Variables
    Dim varItem As Variant

    ' Add all items selected in the listbox to the strCriteria variable with a , between each item

    For Each varItem In Me.lstRecords.ItemsSelected
    strCriteria = strCriteria & "," & Me!lstRecords.ItemData(varItem)
    Next varItem

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    DoCmd.SetWarnings False ' Turn off warnings

    DoCmd.RunSQL "UPDATE Orders SET Orders.Authorised = True WHERE Orders.[OrderID] IN(" & strCriteria & ");"
    DoCmd.RunSQL "UPDATE Orders SET Orders.DateAuthorised = lblDate WHERE Orders.[OrderID] IN(" & strCriteria & ");"

    DoCmd.SetWarnings True ' Turn warnings back on
    DoCmd.RunCommand acCmdSaveRecord ' Save the records
    Me.lstRecords.Requery ' Requery the listbox

    Else
    DoCmd.RunCommand acCmdSaveRecord
    Me.lstRecords.Requery

    End If

    ErrUpdate:
    DoCmd.RunCommand acCmdSaveRecord
    Me.lstRecords.Requery

    -------------------------------

    Hope this helps.
    Last edited by Sferical; 05-15-03 at 06:45.

  5. #5
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    In the code below I have created a table "TBL_delivery_Job_numbers" that I add the job number(s) selected on the list box, I run the query "QRY_Delivery_Clear_Table" to empty the table before use (it's a delete query).

    I use it so that users can select which jobs are being sent on a delivery note to the customer. (the table is linked to a report)

    ***************************

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim varitm As Variant
    Dim ctl As Control


    Set db = CurrentDb()
    Set ctl = Me.LST_Active_jobs
    Set qdf = db.QueryDefs("QRY_Delivery_Clear_Table")

    DoCmd.SetWarnings (False)
    qdf.Execute
    DoCmd.SetWarnings (True)



    Set tdf = db.TableDefs("TBL_Delivery_Job_Numbers")
    Set rst = tdf.OpenRecordset


    For Each varitm In ctl.ItemsSelected
    rst.AddNew
    rst![JOB_Number] = ctl.ItemData(varitm)
    rst.Update
    Next varitm

    ******************************

    hope this helps, I can explain in more detail if you require.

  6. #6
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    I appreciate your help in this matter. Xander, down toward the end of your code, you have

    rst![JOB_Number] = ctl.ItemData(varitm)

    What is [JOB_Number]? Is that the field in the table that matches up with your list box field?

    I am not very familiar with code yet, but I am wading in deeper and deeper all the time. I am amazed what a little bit of code can do.

    Thanks,

    David

  7. #7
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    I appreciate your help in this matter. Xander, down toward the end of your code, you have

    rst![JOB_Number] = ctl.ItemData(varitm)

    What is [JOB_Number]? Is that the field in the table that matches up with your list box field?

    I am not very familiar with code yet, but I am wading in deeper and deeper all the time. I am amazed what a little bit of code can do.

    Thanks,

    David

  8. #8
    Join Date
    Dec 2002
    Location
    Glasgow, UK
    Posts
    100
    [Job_Number] is just the field in the table "TBL_delivery_Job_numbers" - the list box has that value in the first column.

Posting Permissions

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