Create table of field names
Leslie Isaacs
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
Leslie Isaacs wrote:
> 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
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" <stefan....@explido.de> wrote in message
news:%233R0hE3...@TK2MSFTNGP03.phx.gbl...
Stefan Hoffmann
Leslie Isaacs wrote:
> 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 <--
Jeff Boyce
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
http://mvp.support.microsoft.com/
Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
"Leslie Isaacs" <leslie...@gp-n85011.nhs.uk> wrote in message
news:eVWlar2...@TK2MSFTNGP02.phx.gbl...
Leslie Isaacs
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
"Stefan Hoffmann" <stefan....@explido.de> wrote in message
news:OJoCZR3r...@TK2MSFTNGP03.phx.gbl...
Stefan Hoffmann
Leslie Isaacs wrote:
> 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 <--
--
Access-FAQ http://www.donkarl.com/
KnowHow.mdb http://www.freeaccess.de
Newbie-Info http://www.doerbandt.de/Access/Newbie.htm
Klatuu
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
--
Dave Hargis, Microsoft Access MVP
Leslie Isaacs
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
"Stefan Hoffmann" <stefan....@explido.de> wrote in message
news:ux2zTm3r...@TK2MSFTNGP06.phx.gbl...
Leslie Isaacs
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
"Jeff Boyce" <JeffBo...@msn.com-DISCARD_HYPHEN_TO_END> wrote in message
news:uTxnDd3r...@TK2MSFTNGP06.phx.gbl...
Stefan Hoffmann
Leslie Isaacs wrote:
> ... 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
Leslie Isaacs wrote:
> 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
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
"Klatuu" <Kla...@discussions.microsoft.com> wrote in message
news:738B2DF0-2F02-46FD...@microsoft.com...