Using Field Descriptions

[hookomj]

Apologies from a NooB.

I want to create a query that compares the Table's Field.Description with the Form's Field.ToolTip -- and perhaps even populates them?

But my problem is more basic than that, because I can't even succesfully refer to either of these in a query!

Heaps of thanks for any help!

- Jeffrey

[hookomj]

I should clarify: I'd like to get ALL the table's field.descriptions, not just one field's field.description...

- J

[Rawhide]

You need to use either ADOX or DAO 3.6 to be able to access these extended properties.

Check out this how to article from MS: http://support.microsoft.com/?kbid=210314

[hookomj]

Thanks, that's a start!

I got the function to work in debug -- after a few code edits -- but I guess I don't know where to go from there. (feeling dumb)

I THINK I should use that procedure in another procedure that cycles through all the tables/fields, but I'm having trouble constructing that...

Surely I'm doing this the hard way, but...

Function GetAllFieldDesc_DAO()

Dim n, m As Integer 'counters
Dim D As DAO.Database 'part of object I'm cycling through
Dim T As DAO.TableDef 'part of object I'm cycling through
Dim F As DAO.Field 'part of object I'm cycling through
Dim Tabletxt As String 'to pass through to GetFieldDesc_DAO(Tabletxt, Fieldtxt)
Dim Fieldtxt As String 'to pass through to GetFieldDesc_DAO(Tabletxt, Fieldtxt)

Set D = CurrentDb
Tabletxt = ""
Fieldtxt = ""


For n = 0 To Application.CurrentDb.TableDefs.Count
Set T = Application.CurrentDb.TableDefs(n)
Tabletxt = "T.Name"
' MsgBox Tabletxt ' works!

For m = 0 To T.Fields.Count
' ERROR: "Invalid Object or no longer set"
Set F = T.Fields(m)
Fieldtxt = F.Name

MsgBox Tabletxt & "." & _
Fieldtxt & " = " & _
GetFieldDesc_DAO(Tabletxt, Fieldtxt)

Next m
Next n

End Function

(BTW: using A97)

[hermhart]

I found this to work. I set DAO 3.6 per Rawhides's note. I assigned this subroutine to a button on a form. I'm not sure why I had to coerce ST to be string datatype, but for some reason it didn't hold it. The "On Error Resume Next" takes care of the case where some fields might not have descriptions.

Private Sub Command0_Click()
Dim DB As Database, TD As TableDef, FLD As Field, ST As String
Set DB = CurrentDb
Set TD = DB.TableDefs!employees
For Each FLD In TD.Fields
ST = CStr(FLD.Name)
Set FLD = TD.Fields(ST)
On Error Resume Next
Debug.Print FLD.Properties("Description")
Next
End Sub