帮助 - VBA - 搜索功能 - 数据库 MS 访问

Question

• 数据库结构：

用户名字符串

位置字符串

价格数字

• 要求：

  我有一个列表框，里面有一些项目，比如 100 个。用户将在列表框中仅随机选择 10 个（在将 MULTISELECT 更改为 2fmMultiselect 属性之后）。我会有搜索按钮。一旦我选择并单击“搜索”，就必须计算并显示所选商品的总价。

我的搜索代码（感谢亚历克斯先生）

enter code here
Private Sub CommandButton4_Click()

Dim Cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sName As String

Set Cn = New ADODB.Connection
Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\test2.accdb;Persist Security Info=False"
Cn.ConnectionTimeout = 40
Cn.Open

Set rs = New ADODB.Recordset

sName = Replace$(TextBox1.Text, "'", "''")
rs.Open "Select * from SampleTable where UserName = '" & sName & "'", Cn, adOpenForwardOnly, adLockReadOnly, adCmdText

If (rs.EOF) Then
    MsgBox "no match"
Else
    TextBox3.Text = rs("UserName") & " " & rs("Location")
     
    
    rs.Close
    
End If

Set rs = Nothing
Cn.Close
Set Cn = Nothing


End Sub

该代码只是为了搜索并显示在文本框中。

现在，我需要计算用户从列表框中选择的所有 UserName 字段的价格总计。

Answer 1

尝试此操作，但请确保列表框中的绑定列是表的 ID 字段。

Private Function SelectedListString(lstSelected As Access.ListBox) As String
'Loop though a list and get the IDs of all the selected items
'Assumes the bound column is the contains the ID field
    Dim sList As String
    Dim vSelected As Variant        
    With lstSelected
        For Each vSelected In .ItemsSelected
            sList = sList & .ItemData(vSelected) & ","
        Next
    End With
    If sList <> "" Then sList = Left(sList, Len(sList) - 1) 'trim trailing coma
    SelectedListString = sList
End Function

您可以在 SQL 的 IN 语句中使用结果

Dim sSql As String
If myListbox.ItemsSelected.Count > 0 Then
    sSql = "SELECT * FROM table WHERE IDField IN (" & SelectedListString(myListbox) & ")"
End If