帮助 - VBA - 搜索功能 - 数据库 MS 访问 [英] Help - VBA - Search Functionality - Database MS access
问题描述
- 数据库结构:
用户名字符串
位置字符串
价格数量
要求:
我有一些项目的列表框说 100.用户将只在列表框中随机选择 10 个(在将 MULTISELECT 更改为 2fmMultiselect 属性后).我会有搜索按钮.选择并点击搜索后,必须计算并显示所选商品的总价.
I have listbox with some items into it say 100. The user will select only 10 randomly in the listbox (after changing the MULTISELECT to 2fmMultiselect property). I will have search button. Once I select and click Search, the total price of selected items has to be calculated and displayed.
我的搜索代码(感谢 Alex 先生)
My search code ( Thanks to Alex sir)
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
该代码只是为了在文本框中搜索和显示.
That code was just to search and display in a textbox.
现在,我需要计算用户从列表框中选择的所有 UserName 字段的价格.
Now, I need to total the price of what all UserName field selected by the user from listbox.
推荐答案
试试这个,但要确保列表框中的绑定列是表的 ID 字段.
Try this, but make sure the bound column in your list box is the ID field of your table.
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 语句中使用结果
you can use the result in an IN statement in your SQL
Dim sSql As String
If myListbox.ItemsSelected.Count > 0 Then
sSql = "SELECT * FROM table WHERE IDField IN (" & SelectedListString(myListbox) & ")"
End If
这篇关于帮助 - VBA - 搜索功能 - 数据库 MS 访问的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!