Re: Append "Description" property to field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Append the fld object to the Fields collection before setting its
Description. In other words, change

Set fld = .CreateField("CREATED_ID", dbText, 10)
Call setProperty(fld, "Description", "The Login ID of the user who
created the record")
.Fields.Append fld

Set fld = .CreateField("CREATED_DTTM", dbDate)
fld.DefaultValue = "Now()"
Call setProperty(fld, "Description", "The date and time the record
was created")
.Fields.Append fld

to

Set fld = .CreateField("CREATED_ID", dbText, 10)
.Fields.Append fld
Call setProperty(fld, "Description", "The Login ID of the user who
created the record")

Set fld = .CreateField("CREATED_DTTM", dbDate)
fld.DefaultValue = "Now()"
.Fields.Append fld
Call setProperty(fld, "Description", "The date and time the record
was created")


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ray" <Ray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:607E43CF-C9F8-491C-9E7F-651E57C20B83@xxxxxxxxxxxxxxxx
Hi Doug,

I am using Access 97 (client requirement). The line it fails on is
"pObj.Properties(psPropname) = psPropValue".

I tried changing the variable declarations to DAO.Field and DAO.Property
however this didn't correct the problem.

Thanks,

Ray

"Douglas J. Steele" wrote:

What line of code is it complaining about?

You don't mention what version of Access you're using. Note that the code
you've got below uses DAO. Neither Access 2000 nor Access 2002 have a
reference set to DAO by default: you have to add one yourself (select
Tools
| References from the menu bar, scroll through the list of available
references until you find the one for Microsoft DAO 3.6 Object Library,
and
select it.) Access 2003 does have a reference to DAO by default, but it's
lower in precedence that the the reference to ADO. When you've got
references set to both ADO and DAO, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models.

Try changing at least the following declarations, and see whether it
helps:

Dim fld As Field

to

Dim fld As DAO.Field

and

Dim prpNew As Property

to

Dim prpNew As DAO.Property



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ray" <Ray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E66B67EB-7D24-4DA5-9A66-1FC29AADD04E@xxxxxxxxxxxxxxxx
Hi Doug,

I am using the following:

Private Sub addAuditFields()
Dim bCreated As Boolean
Dim db As Database
Dim tdf As TableDef
Dim fld As Field


Set db = CurrentDb

For Each tdf In db.TableDefs
If Left(LCase(tdf.Name), 4) <> "msys" Then
bCreated = False

For Each fld In tdf.Fields
If fld.Name = "CREATED_ID" Then
bCreated = True
Exit For
End If
Next fld

If bCreated = False Then
With tdf
Debug.Print tdf.Name

Set fld = .CreateField("CREATED_ID", dbText, 10)
Call setProperty(fld, "Description", "The Login ID
of
the user who created the record")
.Fields.Append fld

Set fld = .CreateField("CREATED_DTTM", dbDate)
fld.DefaultValue = "Now()"
Call setProperty(fld, "Description", "The date and
time
the record was created")
.Fields.Append fld

.Fields.Append .CreateField("UPDATED_ID", dbText,
10)
.Fields.Append .CreateField("UPDATED_DTTM", dbDate)
End With
End If
End If
Next tdf
End Sub

Private Sub setProperty(pObj As Object, psPropname As String,
psPropValue
As
String)
Dim prpNew As Property
Dim errLoop As Error

On Error GoTo ErrHandler

' Attempt to set the specified property...
pObj.Properties(psPropname) = psPropValue

ProcExit:
On Error GoTo 0
Exit Sub

ErrHandler:
' Error 3270 means that the property was not found.
If DBEngine.Errors(0).Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = pObj.CreateProperty(psPropname, dbText,
psPropValue)
pObj.Properties.Append prpNew
Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr &
errLoop.Description
Next errLoop
End If

Resume ProcExit
Resume
End Sub

Which is basically the example from Access Help.

Cheers,

Ray


"Douglas J. Steele" wrote:

How are you trying to add the description?

The Description property doesn't actually exist by default: you have
to
explicity add it before you can use it.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Ray" <Ray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A4447322-A63A-4F50-9EB6-7E0BB51F3D09@xxxxxxxxxxxxxxxx
I am creating a new table and fields and programmatically and want to
add a
description of the field in a similar manner to the table design
screen
however when I attempt to add the property I get "Rin-time error
3219 -
Invalid operation".

Can anyone help?

Thanks,

Ray








.

Relevant Pages