Results 1 to 3 of 3
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Building a Listbox using field captions (Access 97 / 2k)

    I have a table with quite a few boolean fields, and I would like to dynamically populate a listbox with all these boolean fields. The following code does the job and works ok.
    <pre>Private Sub Form_Load()
    Dim db As DAO.Database
    Dim tabledef As DAO.tabledef
    Dim strTable As String
    Dim strFields As String
    Dim fld As Field
    Set db = CurrentDb()
    strTable = "tblprojects"
    Set tabledef = db.TableDefs(strTable)

    For Each fld In tabledef.Fields
    If fld.Type = dbBoolean Then
    strFields = strFields & fld.Name & ";"
    End If
    Next fld
    strFields = Left(strFields, Len(strFields) - 1)
    Me!List0.RowSource = strFields

    Set db = Nothing
    Set tabledef = Nothing
    Set fld = Nothing
    End Sub
    </pre>


    The field names are not very user friendly, but each field has a Caption property set that is much better. So I would like to make my listbox have two columns, with the field name in first column (hidden) and Caption in second column on display. Again, this works OK if I populate the listbox by writing out a long value list.

    My question is this:

    Is there any way to access the Caption in code so that I can fill my listbox dynamically.

    The obvious doesn't work.
    <pre>strFields = strFields & fld.Name & ";" & fld.Caption & ";" </pre>

    because a DAO field object does not have a caption property.
    Regards
    John



  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Building a Listbox using field captions (Access 97 / 2k)

    <P ID="edit" class=small>(Edited by MarkD on 16-Jan-03 20:38. Fixed dumb mistake.)</P>If the field in question has a caption (as entered in query Field Properties dialog), you can return its value using syntax like this:

    ? CurrentDB.QueryDefs("Query2").Fields("CustomerID") .Properties("Caption").Value
    Customer ID

    NOTES:

    - If query field does not have caption, but this field has caption specified in underlying table, syntax above will return the Caption property specified in Table Design View.

    - If there is no caption specified for field in either table or query, syntax above will result in Error 3270, Property not found. Your procedure would need error handling for this possible (and likely) occurence.

    - If the query field has an alias (using AS keyword in SQL), you have to refer to field by its alias, not its actual name in underlying table. If you refer to aliased field by actual name, it will result in Error 3265, Item not found in this collection (ie, field not found in QueryDef Fields collection).

    - If you assign alias to field with caption specified in table design, the alias will NOT be displayed when you open query, the caption will be displayed. But if referring to field in code, as shown in example above, you must refer to field by its alias.

    - As above notes indicate, a query field's Alias is NOT same as its Caption property.

    Hopefully this clarifies rather than confuses the issue....

    PS: I misread question - I though you were referring to query rather than table fields!! This is syntax for TABLE field Caption property:

    ? CurrentDB.TableDefs("Customers").Fields("CustomerI D").Properties("Caption").Value
    Customer ID

    As with query captions, if no caption has been assigned to field, Error 3270 will result.

    Sorry for mixup!!

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Building a Listbox using field captions (Access 97 / 2k)

    Thanks Mark
    That's just what I needed. I now see that is exactly what my DAO book was trying to tell me.

    I have modified my event to this and it works:
    <pre>Private Sub Form_Load()

    Dim db As DAO.Database
    Dim tabledef As DAO.tabledef
    Dim strTable As String
    Dim strListRowsource As String
    Dim fld As Field
    Dim StrCaption As String
    Set db = CurrentDb()
    strTable = "tblprojects"

    Set tabledef = db.TableDefs(strTable)

    For Each fld In tabledef.Fields
    If fld.type = dbBoolean Then
    strListRowsource = strFields & fld.Name & ";"
    StrCaption = fld.Name
    On Error Resume Next
    StrCaption = fld.Properties("Caption").Value
    strListRowsource = strListRowsource & StrCaption & ";"
    End If
    Next fld
    strListRowsource = Left(strListRowsource, Len(strListRowsource) - 1)
    Me!List1.RowSource = strListRowsource

    Set db = Nothing
    Set tabledef = Nothing
    Set fld = Nothing
    End Sub
    </pre>

    To cope with the absence of a caption, I first set the strcaption to the fld.name, then overwrite this with the caption if it exists. It it doesn't exist Resume Next will just leave the name there.
    Regards
    John



Posting Permissions

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