How can i call the below function to populate my a

Question

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

But this function need arguments according to the VBA conventions.

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

Answer 1

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.

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