- 数据库结构:
用户名字符串
位置字符串
价格数量
-
要求:
我有一个列表框,里面有一些项目,比如100。用户只会在列表框中随机选择10个(将MULTICSELECT更改为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字段的价格。
试试这个方法,但要确保列表框中的绑定列是表的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