在SQL语句中引用的ListBox多选值 [英] Referencing ListBox multiselect values in sql statement

问题描述

我想开一个多选列表框的列中使用的匹配值的记录。此刻我的code仅打开并编辑选择的最后一个记录，我想它来打开所有的人。这是我的code： -

I would like to open a recordset using matching values within a column of a multiselect listbox. At the moment my code only opens and edits the last record of the selection and I would like it to open all of them. Here is my code:-

 Set oRSAppt = Application.CurrentDb().OpenRecordset("Select * FROM [Appointments] WHERE [SlotID] =" & ListBox.Column(7, ListBox.ItemsSelected))
With oRSAppt
    If .BOF = True And .EOF = True Then
        MsgBox "No records found", , "Failed"
        Exit Sub
    Else
    .MoveFirst
    Do While Not .EOF
    .Edit
    .Fields("Status").Value = "Invoiced"
    .Fields("InvoiceID").Value = vInvoiceID
    .Update
    .MoveNext
    Loop
    .Close
    End If
End With

此环节提出了一个for循环，从列表框中得到选择的值 http://msdn.microsoft.com /en-us/library/office/ff823015%28v=office.15%29.aspx 但我不知道如何在SQL语句中还是我甚至应该去了解这种方式做到这一点 - 也许我只是一直在寻找了这么久，我错过了一个显而易见的解决方案。任何帮助将是AP preciated。

This link suggests a for loop to get the selected values from the listbox http://msdn.microsoft.com/en-us/library/office/ff823015%28v=office.15%29.aspx but I am not sure how to do this within the sql statement or whether I should even go about it this way - and maybe I've just been looking at this for so long I've missed an obvious solution. Any help would be appreciated.

推荐答案

您需要首先建立你的SQL语句，是的，你需要使用一个循环。像这样的东西应该做的伎俩：

You will need to build your SQL statement first and yes, you need to use a loop. Something like this should do the trick:

Dim strSQL as String
Dim vItm as Variant
Dim oRSAppt As DAO.Recordset

For Each vItm In Me!Listbox.ItemsSelected
    strSQL = strSQL & ListBox.Column(7, vItm) & ","
Next vItm

strSQL = left(strSQL,len(strSQL) - 1) ' remove last comma

Set oRSAppt = CurrentDb.OpenRecordset("Select * FROM [Appointments] " _
    WHERE [SlotID] In (" & strSQL & ")")

这篇关于在SQL语句中引用的ListBox多选值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！