Field Names

  • Thread starter Thread starter Always Learning
  • Start date Start date
A

Always Learning

Hi Guys,

How do I get a list of field names used in a table.

Thanks for you help.

Best Regards,

Steve.
 
Steve,

Go to menu item Tools > Analyze > Documenter. You'll get the list in an
Excel spreadsheet.

HTH,
Nikos
 
Always Learning said:
Hi Guys,

How do I get a list of field names used in a table.
Click to expand...

Here's some code that should help. You need to build a table (tblFields)
with the following fields:
 
Always Learning said:
Hi Guys,

How do I get a list of field names used in a table.
Click to expand...

Try again. Build a table "tblFields" with the following fields:

ID - AutoNumber
TableName - Text 50
FieldName - Text 50
FieldNumber - Integer
DataType - Text 20

Then paste the following code into a module and run it from the debug window
or a command button.


Function GetFields()
' Arvin Meyer 12/7/1995
' Modified 3/17/2000 Tim Mills-Groninger
' Updated Arvin Meyer 5/19/2004
' Finds all fields in a table

On Error Resume Next

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim intI As Integer
Dim intJ As Integer
Dim strDesc As String

Set db = CurrentDb()
' Clean out the "working" table tblFields
db.Execute "Delete * From tblFields"
' Open the table for data entry
Set rst = db.OpenRecordset("tblFields", dbOpenTable, dbAppendOnly)
' Outer loop for tables
For intI = 0 To db.TableDefs.Count - 1
Set tdf = db.TableDefs(intI)
' Skip system tables
If Left(tdf.Name, 4) <> "MSys" Then
' Now loop through fields
For intJ = 0 To tdf.Fields.Count - 1
Set fld = tdf.Fields(intJ)
rst.AddNew
rst!TableName = tdf.Name
rst!FieldName = fld.Name
rst!FieldNumber = fld.OrdinalPosition
Select Case fld.Type
Case dbDate
rst!DataType = "Date/Time"
Case dbText
rst!DataType = "Text"
Case dbMemo
rst!DataType = "Memo"
Case dbBoolean
rst!DataType = "Yes/No"
Case dbInteger
rst!DataType = "Integer"
Case dbLong
rst!DataType = "Long Integer"
Case dbCurrency
rst!DataType = "Currency"
Case dbSingle
rst!DataType = "Single"
Case dbDouble
rst!DataType = "Double"
Case dbByte
rst!DataType = "Byte"
Case Else
rst!DataType = "Unknown"
End Select
strDesc = ""
' The Resume Next will avoid an error if there is no Description
strDesc = fld.Properties("Description")
rst!Description = IIf(IsNull(fld.Properties("Description")), "",
strDesc)
rst.Update
Next intJ
End If
Next intI
rst.Close
End Function

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Hi Guys,

How do I get a list of field names used in a table.

Thanks for you help.

Best Regards,

Steve.
Click to expand...

Simplest method is to set a combo box Row Source Type to Field List.
Set the combo's Row Source to the name of the table.
 
Always Learning said:
Hi Arvin,

I have created a table called tblFields and put the code into a
module but I keep getting an error (see Attached)
How do I run the function in the module?
Click to expand...

No need to attach a picture, this being a text newsgroup -- the error
message and number, along with the line on which it was raised, would
have been sufficient. You need to add a reference to the Microsoft DAO
3.6 Object Library, which you can do from the VB Editor environment by
clicking Tools -> References..., locating the aforenamed reference in
the list, and putting a check-mark in the box next to it.
 
Back
Top