Create table of field names

[Leslie Isaacs]

Hello All

Is it possible to create a table that contains as its records the names of
all the fields in another table? I am sure it is - but cannot remember how!
Hope someone can help.

Many thanks.
Leslie Isaacs

 

[Stefan Hoffmann]

hi Leslie,

Is it possible to create a table that contains as its records the names of
all the fields in another table? I am sure it is - but cannot remember how!
Hope someone can help.

Take a look at the

TableDefs("yourTable").Fields

collection.


mfG
--> stefan <--

 

[Leslie Isaacs]

Hello Stefan
Thanks for your suggestion.
You'll have to excuse my ignorance I'm afraid: how do I look at the
TableDefs("yourTable").Fields - or rather, how do I actually get the field
names into a new table?
Thanks again.
Les

 

[Stefan Hoffmann]

hi Leslie,

Thanks for your suggestion.
You'll have to excuse my ignorance I'm afraid: how do I look at the
TableDefs("yourTable").Fields - or rather, how do I actually get the field
names into a new table?

For example:

Public Sub Foo(ATableName As String)

Dim db As DAO.Database
Dim fd As DAO.Field
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs(ATableName)

For Each fd in td.Fields
db.Execute "INSERT INTO [yourTable] ([tableName], [fieldName]) " & _
"VALUES ('" & ATableName & "', '" & fd.Name "')"
Next fd

Set td = Nothing
Set db = Nothing

End Sub

mfG
--> stefan <--

 

[Leslie Isaacs]

Hello Stefan
There seems to be a syntax error on the db.Execute line, because it goes red
when I paste in the code. I tried removing the underscore and getting the
whole command on one line, but that made no difference. I have tried to see
what the syntax eror is, but can't!

Also, given that the table with the fields I want is called [all dates], and
the table into which I want to add the fieldnames is called [fields_from_all
dates], with field [fname], would I be right in amending the Set td line
to:

Set td = db.TableDefs([all dates])

and amending the db.Execute line to:

db.Execute "INSERT INTO [fields_from_all dates] ([fields_from_all dates],
[fname]) " & _
"VALUES ('" & [all dates] & "', '" & fd.Name "')"

(after also correcting the syntax error!)

Thanks again for this: I'm learning!
Les

hi Leslie,

Thanks for your suggestion.
You'll have to excuse my ignorance I'm afraid: how do I look at the
TableDefs("yourTable").Fields - or rather, how do I actually get the
field names into a new table?

For example:

Public Sub Foo(ATableName As String)

Dim db As DAO.Database
Dim fd As DAO.Field
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs(ATableName)

For Each fd in td.Fields
db.Execute "INSERT INTO [yourTable] ([tableName], [fieldName]) " & _
"VALUES ('" & ATableName & "', '" & fd.Name "')"
Next fd

Set td = Nothing
Set db = Nothing

End Sub

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Leslie,

Set td = db.TableDefs([all dates])

The parameter here must be a string:

Set td = db.TableDefs("[all dates]")

db.Execute "INSERT INTO [fields_from_all dates] ([fields_from_all dates],
[fname]) " & _
"VALUES ('" & [all dates] & "', '" & fd.Name "')"

(after also correcting the syntax error!)

There is an ampersand missing after fd.Name...



mfG
--> stefan <--

 

[Jeff Boyce]

Leslie

If you'll also post the reasons you wish to do this, the newsgroup readers
may be able to offer alternate approaches.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Klatuu]

Leslie,

I'm with Jeff on this. I can't think of any value to creating such a table.
If you need them for a combo box, A combo box has a row source type property
that allows a field list. It will present a combo list that contains the
field names of a table or query.

If you need it in some other context, here is my version of how to return
and examine field names:

Public Sub ListFieldNames(strTblName)
'Lists the fields in a table
Dim dbf As Database
Dim tdfs As TableDefs
Dim tdf As TableDef
Dim fld As Field
Dim blnFoundIt As Boolean

Set dbf = CurrentDb
Set tdfs = dbf.TableDefs

For Each tdf In tdfs
If tdf.Name = strTblName Then
Debug.Print "Table " & strTblName & " contains " &
tdf.Fields.Count _
& " Fields"
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
blnFoundIt = True
Exit For
End If
Next tdf
If Not blnFoundIt Then
MsgBox "Table " & strTblName & " Not Found in Database", _
vbExclamation, "ListFieldNames"
End If
Set fld = Nothing
Set tdfs = Nothing
Set dbf = Nothing
Set tdf = Nothing
End Sub

 

[Leslie Isaacs]

Hello Stefan

Right - now I have:

Public Sub Foo(ATableName As String)
Dim db As DAO.Database
Dim fd As DAO.Field
Dim td As DAO.TableDef

Set db = CurrentDb
Set td = db.TableDefs("[all dates]")

For Each fd In td.Fields
db.Execute "INSERT INTO [fields_from_all dates] ([fields_from_all dates],
[fname]) " & _
"VALUES ('" & [all dates] & "', '" & fd.name & "')"
Next fd

Set td = Nothing
Set db = Nothing
End Sub

.... which shows up no syntax errors. But then when I try to run the code by
clicking the 'continue' icon at the top of the design view of the module, I
get a window asking me which macro I want to run! I really don't get that.
When I set Foo("all dates") as the OnClick event of a button, I got Compile
error on the referance to [all dates] just after the VALUES expression.

Sorry if I'm making heavy weather of this. I really am trying to get it
right!

Many thanks for your continued help.
Les

hi Leslie,

Set td = db.TableDefs([all dates])

The parameter here must be a string:

Set td = db.TableDefs("[all dates]")

db.Execute "INSERT INTO [fields_from_all dates] ([fields_from_all dates],
[fname]) " & _
"VALUES ('" & [all dates] & "', '" & fd.Name "')"

(after also correcting the syntax error!)

There is an ampersand missing after fd.Name...



mfG
--> stefan <--

 

[Leslie Isaacs]

Jeff

Hello!

Many thanks for your suggestion.

My reason for wanting to do this relates to the post I made this morning on
the formscoding newsgroup (to which I haven't had a reply yet!), headed
"Problems with filtering continuous form".
Essentially, I need to provide the user with a combobox that can be used to
select a particular field from table [all dates].
Having selected the required field, the user should then be able to see all
the records for that field - subject to two filters.

Hope that helps: I would certainly be interested in any other (simpler?) way
of doing what's needed!

Thanks
Les

 

[Stefan Hoffmann]

hi Leslie,

... which shows up no syntax errors. But then when I try to run the code by
clicking the 'continue' icon at the top of the design view of the module, I
get a window asking me which macro I want to run! I really don't get that.
When I set Foo("all dates") as the OnClick event of a button, I got Compile
error on the referance to [all dates] just after the VALUES expression.

Create a table named "yourTable" with the two fields "tableName" and
"fieldName", both of type Text(255).

Take the first version of my procedure and add the missing ampersand
after fd.Name.

Go to the immediate window and run

Foo "[all dates]"


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Leslie,

Hope that helps: I would certainly be interested in any other (simpler?) way
of doing what's needed!

Ahh, Klatuu was right. Read his post about the Field List in a ComboBox.

mfG
--> stefan <--

 

[Leslie Isaacs]

Hello Dave

Well blow me: I never knew that!!!
I have just set the combobox's row source type to Field list, and the Row
source to All dates ... and hey presto!!
Is it possible to limit the field names presented in the combobox: in fact
it would be ideal if I could exclude 5 of the fields. If this isn't possible
it's no big deal.

The bigger problem is how I set the value of the textbox ([text0]) in the
form's detail section according to the value selected in my newly defined
combobox [combo1].
I can do this with a Case statement, for each of the possible field names
tnat can be selected: then I get

Select Case [Combo1].Value
Case "ver k"
[Text0].ControlSource = "=[ver k]"
If [Frame12] = 1 Then
Me.Filter = "right([ver k],1) <> ""h"""
Else
If [Frame12] = 2 Then
Me.Filter = "right([ver k],1) = ""h"""
Else
Me.Filter = "[ver k] is not null"
End If
End If
Me.FilterOn = True

Case "ves d"
etc

.... but obviously this is going to be extremely repetitive. Better would be
something like the following (which I can't get to work):

[Text0].ControlSource = "=[combo1]"
If [Frame12] = 1 Then
Me.Filter = "right([combo1],1) <> ""h"""
Else
If [Frame12] = 2 Then
Me.Filter = "right([combo1],1) = ""h"""
Else
Me.Filter = "[combo1] is not null"
End If
End If

.... then I wouldn't need the Case statement.

I would be extremely grateful for any further help.
Many thanks
Les