我需要从访问表填充访问表单列表框。
以下是我在按钮单击事件上复制粘贴的代码:
Public Sub PopulateLBWithData(DBPath As String, _
TableName As String, FieldName As String, _
oListControl As Object,Optional Distinct As Boolean = False, _
Optional OrderBy As String)
''#PURPOSE: Populate a list box, combo box
''#or control with similar interface with data
''#from one field in a Access Database table
''#Parameters: DBPath: FullPath to Database
''#TableName: The Name of the Table
''#FieldName: Name of the Field
''#Distinct: Optional -- True if you want distinct value
''#Order By: Optional -- Field to Order Results by
''#Must have reference to DAO in your project
Dim sSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim td As DAO.TableDef
Dim f As DAO.Field
Dim sTest As String
Dim bIsNumeric As Boolean
Dim i As Integer
On Error Resume Next
''#validate all parameters
oListControl.AddItem "a"
oListControl.Clear
If Err.Number > 0 Then Exit Sub
sTest = Dir(DBPath)
If sTest = "" Then Exit Sub
Set db = Workspaces(0).OpenDatabase(DBPath)
If Err.Number > 0 Then Exit Sub
Set td = db.TableDefs(TableName)
If Err.Number > 0 Then
db.Close
Exit Sub
End If
Set f = td.Fields(FieldName)
If Err.Number > 0 Then
db.Close
Exit Sub
End If
If Len(OrderBy) Then
Set f = td.Fields(OrderBy)
If Err.Number > 0 Then
db.Close
Exit Sub
End If
End If
sSQL = "SELECT "
If Distinct Then sSQL = sSQL & "DISTINCT "
sSQL = sSQL & "[" & FieldName & "] FROM [" & TableName & "]"
If OrderBy <> "" Then sSQL = sSQL & " ORDER BY " & OrderBy
Set rs = db.OpenRecordSet(sSQL, dbOpenForwardOnly)
With rs
Do While Not .EOF
oListControl.AddItem rs(FieldName)
.MoveNext
Loop
.Close
End With
db.Close
End Sub
但根据 VBA 约定,该函数需要参数。
请帮助我如何调用此函数从同一访问表填充我的 VBA 表单列表框?
对于您可能想要做的事情来说,该代码过于复杂。
为什么不尝试只设置控件的行源,然后重新查询。
如果要保留参数化,则传入SQL。
Dim strSQL As String
strSQL = "SELECT MyField FROM MyTable;"
Me.lstMyListBox.RowSource = strSQL
Me.lstMyListBox.Requery
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)