在一个小Access的项目中遇到需要获取ListBox选中记录的信息的问题,现将解决过程分享出来。
做一个例子来实现怎么在Access中获取ListBox选中的记录,多选的话各个记录中间用“,”分割开。
1、打开一个access数据库,模板数据库也可以。
2、创建一个Form。
3、在Form中添加下列控件,并且按下列说明设置控件的属性。
List Box
----------------------------------------------------
Name : ListName
Row Source Type : Table/Query
Row Source : SELECT FieldName FROM TableName(SQL语句,可以按向导设置)
Multi Select : Extended
Width : 3.5"
Height : 0.75"
Text Box
-----------------------
Name : SelectedItem
Width : 3.5"
Height : 0.25"
Command Button
----------------------------------
Name : testselected
Caption : Display Selected Items
Width : 1.375"
Height : 0.3"
Command Button
----------------------
Name : ClrList
Caption : Clear List
Width : 1.375"
Height : 0.3"
4、随便选中一个控件,在属性里面选择事件,点击进去Code界面,把下面的代码粘贴到Visual Basic for Application Editer 里面。
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
Dim sTemp As String
Dim sValue As String
Dim sChar As String
Dim iCount As Integer
Dim iListItemsCount As Integer
sTemp = Nz(Me!SelectedItem.Value, " ")
iListItemsCount = 0
bFound = False
iCount = 0
Call clearListBox
For iCount = 1 To Len(sTemp) + 1
sChar = Mid(sTemp, iCount, 1)
If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
bFound = False
Do
If StrComp(Trim(Me!ListName.ItemData(iListItemsCount)), Trim(sValue)) = 0 Then
Me!ListName.Selected(iListItemsCount) = True
bFound = True
End If
iListItemsCount = iListItemsCount + 1
Loop Until bFound = True Or iListItemsCount = Me!ListName.ListCount
sValue = ""
Else
sValue = sValue & sChar
End If
Next iCount
End Sub
Private Sub clearListBox()
Dim iCount As Integer
For iCount = 0 To Me!ListName.ListCount
Me!ListName.Selected(iCount) = False
Next iCount
End Sub
Private Sub testselected_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer
iCount = 0
If Me!ListName.ItemsSelected.Count <> 0 Then
For Each oItem In Me!ListName.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!ListName.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & "," & Me!ListName.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If
Me!SelectedItem.Value = sTemp
End Sub
Private Sub clrList_Click()
Call clearListBox
Me!SelectedItem.Value = Null
End Sub
5、关闭Visual Basic Editer 并且保存Form。
6、以Form View的形式打开这个Form,在ListBox控件里面选择记录(按Ctrl键可以多选)。
7、点击Display Selected Items按钮就可以在Text控件里面显示出各个记录。