Adding Field Descriptions when using Create Field Method

[Graham M Haines]

Is it possible to add the field description when creating Fields using the
Create Fields Method. I am creating a series of tables, where the field
information is taken from a table:

TABLE NAME FIELD NAME FIELD DESCRIPTION
FIELD LENGTH FIELD TYPE
Institution INSTAPP Indicator for HEFCE funding
approximations 2 dbinteger
Institution RECID Record type indicator
10 dbtext
Institution UKPRN UK Provider Reference Number
20 dbtext


I can specify all the details using the Fields.Append.CreateFields using
variables as below, but I would also like to add in the field descriptions
to the tables as well.

. . . . .
Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]

With tdf
.Fields.Append .CreateField(strfieldname, strtype, intfldsize)
End With
Next intcnt
End With
mdb.TableDefs.Append tdf
--
Regards

Graham

Graham Haines

 

[Douglas J. Steele]

Dim prp As DAO.Property

Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]
strdescription = ![Description]
With tdf
.Fields.Append .CreateField(strfieldname, strtype, intfldsize)
Set prp = tdf.Fields(strfieldname).CreateProperty( _
"Description", dbText, strdescription)
tdf.Fields(strfieldname).Properties.Append prp
End With
Next intcnt
End With
mdb.TableDefs.Append tdf

 

[Graham M Haines]

Thanks Doug

When I added this into the procedure I get a "Run Time Error '3129' -
~Invalid Operation" on the line of code:

tdf.Fields(strfieldname).Properties.Append prp

Dim prp As DAO.Property

Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]
strdescription = ![Description]
With tdf
.Fields.Append .CreateField(strfieldname, strtype, intfldsize)
Set prp = tdf.Fields(strfieldname).CreateProperty( _
"Description", dbText, strdescription)
tdf.Fields(strfieldname).Properties.Append prp
End With
Next intcnt
End With
mdb.TableDefs.Append tdf


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is it possible to add the field description when creating Fields using
the Create Fields Method. I am creating a series of tables, where the
field information is taken from a table:

TABLE NAME FIELD NAME FIELD DESCRIPTION FIELD LENGTH FIELD
TYPE
Institution INSTAPP Indicator for HEFCE funding
approximations 2 dbinteger
Institution RECID Record type indicator 10
dbtext
Institution UKPRN UK Provider Reference Number
20 dbtext


I can specify all the details using the Fields.Append.CreateFields using
variables as below, but I would also like to add in the field
descriptions to the tables as well.

. . . . .
Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]

With tdf
.Fields.Append .CreateField(strfieldname, strtype, intfldsize)
End With
Next intcnt
End With
mdb.TableDefs.Append tdf
--
Regards

Graham

Graham Haines

 

[Arvin Meyer [MVP]]

The description property for a field does not exist until you create it. You
might try this:

Sub CreateFieldProp (fld As Field, strPropName As String, _
intPropType As Integer, varPropValue As Variant)
' Set field property without producing error

Dim prp As Property
On Error Resume Next

fld.Properties(strPropName) = varPropValue

If Err <> 0 Then ' Error occurred when value set.
If Err <> 3265 And Err <> 3270 Then
On Error GoTo 0
MsgBox "Couldn't set property '" & strPropName _
& "' on field '" & fld.name & "'", 48, "CreateFieldProp"
Else
On Error GoTo 0
Set prp = fld.CreateProperty(strPropName, _
intPropType, varPropValue)
fld.Properties.Append prp
End If
End If
End Sub

Then to set a field description:

Dim db As Database, fld As Field
Set db = CurrentDb()
Set fld = db.Tabledefs("MyTable").Fields("fld")
CreateFieldProp fld, "Description", dbText, _
"This is a description for the field"

 

[Douglas J. Steele]

Sorry about that. Looks as though you have to create the table first, then
go back and add the description.

Dim prp As DAO.Property

Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]
With tdf
.Fields.Append .CreateField(strfieldname, strtype, intfldsize)
End With
Next intcnt
End With
mdb.TableDefs.Append tdf
rst1.MoveFirst
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]
strdescription = ![Description]
With tdf.Fields(strfieldname)
Set prp = .CreateProperty( _
"Description", dbText, strdescription)
.Properties.Append prp
End With
Next intcnt
End With



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Doug

When I added this into the procedure I get a "Run Time Error '3129' -
~Invalid Operation" on the line of code:

tdf.Fields(strfieldname).Properties.Append prp

Dim prp As DAO.Property

Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]
strdescription = ![Description]
With tdf
.Fields.Append .CreateField(strfieldname, strtype, intfldsize)
Set prp = tdf.Fields(strfieldname).CreateProperty( _
"Description", dbText, strdescription)
tdf.Fields(strfieldname).Properties.Append prp
End With
Next intcnt
End With
mdb.TableDefs.Append tdf


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is it possible to add the field description when creating Fields using
the Create Fields Method. I am creating a series of tables, where the
field information is taken from a table:

TABLE NAME FIELD NAME FIELD DESCRIPTION FIELD LENGTH FIELD
TYPE
Institution INSTAPP Indicator for HEFCE funding
approximations 2 dbinteger
Institution RECID Record type indicator 10
dbtext
Institution UKPRN UK Provider Reference
Number 20 dbtext


I can specify all the details using the Fields.Append.CreateFields using
variables as below, but I would also like to add in the field
descriptions to the tables as well.

. . . . .
Set tdf = mdb.CreateTableDef(strtablename)
With rst1
For intcnt = 1 To intreccnt
.Move intmov1
intmov1 = 1
strfieldname = ![FIELD NAME]

With tdf
.Fields.Append .CreateField(strfieldname, strtype,
intfldsize)
End With
Next intcnt
End With
mdb.TableDefs.Append tdf
--
Regards

Graham

Graham Haines