CurrentDb.TableDefs(sTableName).Fields(sFieldName) is a reference to the
Field object. The only way to use it by itself would be:
Dim fldCurr As Field
Set fldCurr = CurrentDb.TableDefs(sTableName).Fields(sFieldName)
If you want a property of that object, you need to refer to the property.
Assuming you're trying to get the Description property, your code needs to
be:
Sub test1()
Dim sTableName As String
Dim sFieldName As String
Dim sdesc As String
sTableName = "tblAudit"
sFieldName = "audType"
sdesc =
CurrentDb.TableDefs(sTableName).Fields(sFieldName).Properties("Description")
MsgBox sdesc
End Sub
Note that Description is an odd property: it doesn't actually exist unless a
description has been assigned to the field. If you hadn't actually assigned
a description to the field, you'll get an error 3270 ("Property not found.")
A common way to handle that, then, would be
Sub test1()
On Error GoTo ErrHandler
Dim sTableName As String
Dim sFieldName As String
Dim sdesc As String
sTableName = "tblAudit"
sFieldName = "audType"
sdesc =
CurrentDb.TableDefs(sTableName).Fields(sFieldName).Properties("Description")
ExitHere:
MsgBox sdesc
Exit Sub
ErrHandler:
Select Case Err.Number
Case 3270
sdesc = "***No Description Given***"
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
Resume ExitHere
End Sub
I'm assuming from your code that you haven't told Access to require that all
variables be declared. To me, that's a huge mistake. Go into the VB Editor
and select Tools | Options from the menu. On the Editor tab, make sure that
the "Require Variable Declaration" check box is selected. That will ensure
that all new modules will have a line "Option Explicit" near the top.
(Unfortunately, you'll have to go into all existing modules and add that
line yourself). While it may seem a pain having to declare each variable, it
can save you hours in the long run trying to track down why your code isn't
doing what it's supposed to when you accidentally mistyped one of the
variable names!
