L
Leslie Isaacs
Hello All
I have a table called [all dates], and a combobox called [combo1] on a form
called [frm scheduler wkhc].
I want to populate [combo1] with the fieldnames from [all dates].
At http://www.freevbcode.com/ShowCode.Asp?ID=184 I have found a function
(below) which I am sure is exactly what I need - but I cannot get it to
work!
When I type
?PopulateFieldListControl "C:\WKHC DATABASE\telnumbers.mdb","[frm scheduler
wkhc]![Combo1]",sTables
in the immediate window I just get "Compile error - argument not optional",
but I don't understand this because the final argument is declared as
optional - and even if I put it in (as True or False) I still get the same
error.
Hope someone can help.
Many thanks
Les
The code:
Option Compare Database
Public Sub PopulateFieldListControl(DBPath As String, _
ListControl As Object, TableNames() As String, _
Optional Qualify As Boolean = False)
'Purpose: Populate a List Box with the names of fields
'in one or more Access Database Tables
'PURPOSE: Populate a list box, combo box
'or control with similar interface with the
'names of fields in one or more Access
'Database Tables
'Parameters:
'DBPath: FullPath to Database
'List Control: the list or combo box
'TableNames: String Array containing
'names of fields you want to include
'Qualify: (Optional) Set to true if you
'want to qualify the field name with the
'table name (e.g., MyTable.MyField, as
'opposed to MyField)
'Example Usage
Dim sTables As String
sTables(0) = "all dates"
'sTables(1) = "Table2"
'PopulateFieldListControl "C:\WKHC DATABASE\telnumbers.mdb", Combo1, sTables
'Combo1 will contain all the fieldnames in Table1 and Table2
'Must have reference to DAO in your project
Dim lCtr As Long, lCnt As Long
Dim oFields As Collection
Dim i As Integer
Dim sItem As String, sTest As String
Dim iTableStart As Integer, iTableEnd As Integer
Dim db As DAO.Database
Dim td As DAO.TableDef
iTableStart = LBound(TableNames)
iTableEnd = UBound(TableNames)
On Error Resume Next
'Validate everyting
ListControl.AddItem "a"
ListControl.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
For i = iTableStart To iTableEnd
Set td = db.TableDefs(TableNames(i))
If Err.number > 0 Then
db.Close
Exit Sub
End If
Next
For i = iTableStart To iTableEnd
Set td = db.TableDefs(TableNames(i))
Set oFields = FieldNames(td)
lCnt = oFields.Count
For lCtr = 1 To lCnt
sItem = IIf(Qualify, TableNames(i) & "." & _
oFields(lCtr), oFields(lCtr))
ListControl.AddItem sItem
Next
Next
db.Close
End Sub
Private Function FieldNames(td As DAO.TableDef) As Collection
Dim oCol As New Collection
Dim i As Integer
For i = 1 To td.Fields.Count
oCol.Add td.Fields(i - 1).name
Next
Set FieldNames = oCol
End Function
I have a table called [all dates], and a combobox called [combo1] on a form
called [frm scheduler wkhc].
I want to populate [combo1] with the fieldnames from [all dates].
At http://www.freevbcode.com/ShowCode.Asp?ID=184 I have found a function
(below) which I am sure is exactly what I need - but I cannot get it to
work!
When I type
?PopulateFieldListControl "C:\WKHC DATABASE\telnumbers.mdb","[frm scheduler
wkhc]![Combo1]",sTables
in the immediate window I just get "Compile error - argument not optional",
but I don't understand this because the final argument is declared as
optional - and even if I put it in (as True or False) I still get the same
error.
Hope someone can help.
Many thanks
Les
The code:
Option Compare Database
Public Sub PopulateFieldListControl(DBPath As String, _
ListControl As Object, TableNames() As String, _
Optional Qualify As Boolean = False)
'Purpose: Populate a List Box with the names of fields
'in one or more Access Database Tables
'PURPOSE: Populate a list box, combo box
'or control with similar interface with the
'names of fields in one or more Access
'Database Tables
'Parameters:
'DBPath: FullPath to Database
'List Control: the list or combo box
'TableNames: String Array containing
'names of fields you want to include
'Qualify: (Optional) Set to true if you
'want to qualify the field name with the
'table name (e.g., MyTable.MyField, as
'opposed to MyField)
'Example Usage
Dim sTables As String
sTables(0) = "all dates"
'sTables(1) = "Table2"
'PopulateFieldListControl "C:\WKHC DATABASE\telnumbers.mdb", Combo1, sTables
'Combo1 will contain all the fieldnames in Table1 and Table2
'Must have reference to DAO in your project
Dim lCtr As Long, lCnt As Long
Dim oFields As Collection
Dim i As Integer
Dim sItem As String, sTest As String
Dim iTableStart As Integer, iTableEnd As Integer
Dim db As DAO.Database
Dim td As DAO.TableDef
iTableStart = LBound(TableNames)
iTableEnd = UBound(TableNames)
On Error Resume Next
'Validate everyting
ListControl.AddItem "a"
ListControl.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
For i = iTableStart To iTableEnd
Set td = db.TableDefs(TableNames(i))
If Err.number > 0 Then
db.Close
Exit Sub
End If
Next
For i = iTableStart To iTableEnd
Set td = db.TableDefs(TableNames(i))
Set oFields = FieldNames(td)
lCnt = oFields.Count
For lCtr = 1 To lCnt
sItem = IIf(Qualify, TableNames(i) & "." & _
oFields(lCtr), oFields(lCtr))
ListControl.AddItem sItem
Next
Next
db.Close
End Sub
Private Function FieldNames(td As DAO.TableDef) As Collection
Dim oCol As New Collection
Dim i As Integer
For i = 1 To td.Fields.Count
oCol.Add td.Fields(i - 1).name
Next
Set FieldNames = oCol
End Function