Assign table field Descriptions

[lamago]

Hi,

I have a table with 200 fields for which I want to add descriptions. I have another table with two fields. FieldN and FieldDescription that lists the descriptions of each field in order of apperance. Is there any code out there that can allow me to do this easily?

Thanks in advance for your help/suggestions

Margarita

 

[lamago]

Hi again, I came up with this, but when it gets to "fld.Properties.Append prp" it says "Invalid Operation" Any ideas?

Function ChangeField()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim dsp As String
Dim prp As DAO.Property
Dim sqlstr, tbllayout As String

tbllayout = "AYPReportsLayout"

'Open Database
sqlstr = "select * from " & tbllayout & ";"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
rs.MoveFirst
While Not rs.EOF
Set fld = rs.Fields("FieldName")
dsp = rs.Fields("Description")
Set prp = fld.CreateProperty("Description", dbText, dsp)
fld.Properties.Append prp
rs.MoveNext

Wend
'Close Database
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

 

[lamago]

Its me again, I got it, this works. But if it already has a description it says "Cannot Append already exits" How can I make it to overwrite it? Any Ideas are appreciated


Function ChangeField()
Dim db As Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim dsp As String, fldname As String
Dim prp As DAO.Property
Dim sqlstr As String


'Open Database
sqlstr = "select * from AYPReportsLayout;"
Set db = CurrentDb
Set rs = db.OpenRecordset(sqlstr)
Set tdf = db.TableDefs!AYPReports

rs.MoveFirst
While Not rs.EOF
fldname = rs.Fields("FieldName")
dsp = rs.Fields("Description")
Set fld = tdf.Fields(fldname)
Set prp = fld.CreateProperty("Description", dbText, dsp)
fld.Properties.Append prp
rs.MoveNext

Wend
'Close Database
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Function

 

[PHV]

Play with the On Error instruction and the Err object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gol4]

or the isobject

If Not IsObject(fld.Properties("Description")) Then
Set prp = fld.CreateProperty("Description", dbText, dsp)
fld.Properties.Append prp
Else
fld.Properties("Description") = dsp
End If

 

[TheAceMan1]

How are ya lamago . . . . .

To get around the errors when you [blue]Set,Get,Create field properties[/blue], the following function either [blue]returns the property value or Empty if the property doesn't exist.[/blue] Note . . . you need to specify [blue]Dim db As DAO.Database[/blue] in your decalarations. Just be aware if you assign the function to a variable it has to be a variant.

[blue]Public Function GetTblProp(tblName As String, fldName As String, prpName As String)
   [green]'Always assign GetTblProp to Variant data type.
   'Returns Empty if property not found.
   'Use IsEmpty() to determine if property exists.
   'Returns property value otherwise[/green]
   
On Error GoTo GotErr
   
   Dim db As DAO.Database
   Set db = CurrentDb
   GetTblProp = db.TableDefs(tblName)(fldName).Properties(prpName)

GotErr:
   Set db = Nothing
   
End Function[/blue]

An example of use:

[blue]   Dim Rtn
  
   Rtn = GetTblProp("[purple][b]tblName[/b][/purple]", "[purple][b]fldName[/b][/purple]", "[purple][b]prpName[/b][/purple]")
   
   If IsEmpty(Rtn) Then
      [green]'Create Property![/green]
   Else
      [green]'Directly Set Property or other code[/green]
   End If[/blue]

See Ya! . . . . . .