Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2000 Field's Caption Property

    By Opening a table in design view it's possible to set a field's caption property but how can I access the caption property in VBA so as to, e.g., use it in a textbox in a report?

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 Field's Caption Property

    this code will print out field name & description - in this case for a table called articles. might help you forward

    Sub FieldInfo()
    Dim dbs As Database, tdf As TableDef
    Dim fld As Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!articles

    For Each fld In tdf.Fields
    Debug.Print fld.name, fld.Properties("description")
    Next fld
    End Sub

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No Can Do

    This gives me the description of the field not the caption.
    Other leads?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Can Do

    Just use the syntax fld.Properties("caption")
    to retrieve the caption property.
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Can Do

    use
    fld.Properties("caption")

    but it seems to need something in the caption before it can find it - so don't know if you could set a null caption

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Can Do

    I'm working with ADO and even following your advice I don't seem to be able to access the caption property.
    I've opened a table named "Vax" already containing a field named "Povo" and tried to set the field's caption property using the following sintax:

    Set cnn = CurrentProject.Connection
    rst.CursorLocation = adUseClient
    rst.Open "Vax", cnn, adOpenKeyset, adLockOptimistic
    With rst
    .Fields("Povo").Properties("caption") = "Povox"
    End With

    But all I get is an error message:
    "Item cannot be found in the collection corresponding to the requested name or ordinal."
    What should I to access the caption property using ADO?

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Can Do

    I'm working with ADO and even following your advice I don't seem to be able to access the caption property.
    I've opened a table named "Vax" already containing a field named "Povo" and tried to set the field's caption property using the following sintax:

    Set cnn = CurrentProject.Connection
    rst.CursorLocation = adUseClient
    rst.Open "Vax", cnn, adOpenKeyset, adLockOptimistic
    With rst
    .Fields("Povo").Properties("caption") = "Povox"
    End With

    But all I get is an error message:
    "Item cannot be found in the collection corresponding to the requested name or ordinal."
    What should I to access the caption property using ADO?

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    Yorkshire, England
    Posts
    82
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Can Do

    try this function - change table name "articles" and field name title to suit

    call setcaption("dummy") from the debug box and it will print out the current caption then cahange it to "dummy fieldname" then print it out again to check

    Function SetCaption(ByVal x As String)

    Dim dbs As Database, tdf As TableDef
    Dim fld As Field

    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs!articles

    Set fld = tdf.Fields("title")
    Debug.Print fld.name, fld.Properties("caption")
    fld.Properties("caption") = x & fld.name
    Debug.Print fld.name, fld.Properties("caption")

    End Function

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Can Do

    If you try the SetCaption function using ADO instead of DAO, you'll see that it doesn't work, giving the message,"Item cannot be found in the collection corresponding to the requested name or ordinal."

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Can Do

    It makes it easier when you specify the object model up front. You'll need to open a catalog and a table object and retrieve the caption from the table's columns. Here's a routine that will at least enumerate the properties of a table object's fields. You'll see, though, that properties like caption are extended properties and provider-specific, so it isn't as easy to reference them in ADO. The property names you'll see may be long strings with punctuation in them, but that's what you have to use to reference that particular property for that provider.

    <pre>Public Sub EnumFieldProps(ByVal strTblName As String)
    'created by Charlotte Foust
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim prp As ADOX.Property

    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    cat.ActiveConnection = CurrentProject.Connection

    Set tbl = cat.Tables(strTblName)
    With tbl
    For Each col In tbl.Columns
    Debug.Print col.Name, col.Type
    For Each prp In col.Properties
    Debug.Print , prp.Name, prp.Type, prp.Value
    Next prp
    End If
    Next col
    End With
    On Error Resume Next
    Set prp = Nothing
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
    End Sub 'EnumFieldProps(ByVal strTblName As String)</pre>

    Charlotte

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No Can Do

    Your Sub EnumFieldProps lists field property names(the properties listed are the same for all fields in the table) but, although, e.g., the Description property is present, there's no sign of any Caption property. (see attached file)
    Back to square one?
    Attached Files Attached Files

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: No Can Do

    Thinking it over, I believe that caption is one of the properties that doesn't have an ADO equivalent. It should, but I've never found one.
    Charlotte

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Aaargh! ADO Let Me Down!

    Woe is me <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •