Populate combobox from fieldnames

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
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
 
Have you tried changing the combo boxes "Row Source Type" to Field List?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Leslie Isaacs said:
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
Click to expand...
 
Hello Dale

Thanks for your suggestion, I hadn't changed the combo boxe's "Row Source
Type" to Field List, but I have now - and it didn't make any difference!
If you have any other suggestions I would be really grateful.

Thanks again
Les



Dale Fye said:
Have you tried changing the combo boxes "Row Source Type" to Field List?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Leslie Isaacs said:
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
Click to expand...
Click to expand...
 
Back
Top