Is it possible to create field descriptions from variable text?

[Guest]

I would like to create automated field descriptions for a series of variables
in an Access database. Is it possible to create these from text strings
stored in another table?

For instance, I have a list of questions in one table and I would like to
populate the descriptions of fields in another table with these questions.

Any suggestions?

 

[Brendan Reynolds]

The Description property is a Jet extended property that isn't created until
the first time a value is assigned to it, so we need to either assign a
value to the property, or create it, depending on whether it already exists
or not. Here's an example that adds or modifies the property for each field
in the Categories table in the Northwind sample MDB ...

Public Sub WriteFieldDescriptions()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim boolPropFound As Boolean

Set db = CurrentDb
Set tdf = db.TableDefs("Categories")
For Each fld In tdf.Fields
boolPropFound = False
For Each prp In fld.Properties
If prp.Name = "Description" Then
boolPropFound = True
prp.Value = fld.Name & " - updated description"
Exit For
End If
Next prp
If boolPropFound = False Then
Set prp = fld.CreateProperty("Description", _
dbText, fld.Name & " - updated description")
fld.Properties.Append prp
End If
Next fld

End Sub

 

[Guest]

Thanks Brendan,

The script is very useful and gets me part the way there, but I still have
the problem that the text for the description is still coming from a field
name rather than the text from another table. For example, if I have the
following fields in a table:

Name, Age, Address and Occupation

In another table I have a list of the questions that were asked for each
field (corresponding to each variable name), e.g.

Name, What is your name?
Age, What is your age?
Address, What is your address?
Occupation, What is your occupation?

I want to add the above text to the description property for each of the
fields. Obviously the field names are not as self explanatory as this example
and at the moment I have to enter the text for each one.

I will try and modify your code, but if you have any other ideas, that will
be really useful.

Thanks, PJW

 

[Brendan Reynolds]

I used the field name as part of the description merely for illustrative
purposes. If you have the descriptions stored in a table, then you need to
open a recordset on that table, retrieve the descriptions from the
recordset, and assign them to the Description property. In the example
below, 'tblSource' is the table that stores the text, and 'tblTarget' is the
table containing the fields to the Description property of which we will
assign the text ...

Public Sub SetDescrip()

Dim db As DAO.Database
Dim rstSource As DAO.Recordset
Dim tdfTarget As DAO.TableDef
Dim fldTarget As DAO.Field
Dim prpTarget As DAO.Property
Dim boolPropFound As Boolean

On Error GoTo ErrorHandler
Set db = CurrentDb
Set rstSource = db.OpenRecordset("SELECT FieldName, Description FROM
tblSource")
Set tdfTarget = db.TableDefs("tblTarget")
For Each fldTarget In tdfTarget.Fields
rstSource.FindFirst ("FieldName = '" & fldTarget.Name & "'")
If Not rstSource.NoMatch Then
boolPropFound = False
For Each prpTarget In fldTarget.Properties
If prpTarget.Name = "Description" Then
boolPropFound = True
prpTarget.Value = rstSource.Fields("Description")
Exit For
End If
Next prpTarget
If Not boolPropFound Then
Set prpTarget = fldTarget.CreateProperty("Description",
dbText, rstSource.Fields("Description"))
fldTarget.Properties.Append prpTarget
End If
End If
Next fldTarget

ExitProcedure:
On Error Resume Next
If Not rstSource Is Nothing Then
rstSource.Close
End If
Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProcedure

End Sub