> 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[ at ]discussions.microsoft.com> wrote in message
>> news:E66B67EB-7D24-4DA5-9A66-1FC29AADD04E[ at ]microsoft.com...
>> > 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[ at ]discussions.microsoft.com> wrote in message
>> >> news:A4447322-A63A-4F50-9EB6-7E0BB51F3D09[ at ]microsoft.com...
>> >> >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
>> >>
>> >>
>> >>
>>
>>
>>