Code:
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