Is it possible to create field descriptions from variable text?

P

PJW

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?
 
B

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
 
P

PJW

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
 
Top