我如何调用以下函数来填充我的访问表单列表控件 [英] How can i call the below function to populate my access form list control

问题描述

我需要从访问表中填充访问表单列表框.

I need to populate the access form list box from a access table.

下面是我在按钮单击事件中复制粘贴的代码:

Below is the code which I copy-pasted on button click event:

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约定的参数.

But this function need arguments according to the VBA conventions.

请帮助我如何调用此函数以从同一访问表填充vba表单列表框?

Please help me how i can call this function to populate my vba form list box from the same access table?

推荐答案

对于您可能想要执行的操作，该代码过于复杂.

That code is overly complex for what you're probably trying to do.

为什么不尝试仅设置控件的行源，然后重新查询.

Why not try to just set the control's row source and then requery.

如果要保留参数化，请传入SQL.

If you want to retain the parameterization, then pass in the SQL.

Dim strSQL As String

strSQL = "SELECT MyField FROM MyTable;"

Me.lstMyListBox.RowSource = strSQL
Me.lstMyListBox.Requery

这篇关于我如何调用以下函数来填充我的访问表单列表控件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！