Form labels - possible to take from table field description?

[HGCanada]

I am creating a form based on a table which has meaningless field names (except for data analysts), but the field descriptions are detailed and would be very helpful to include on the form.

For example, the field name might be "I_A", which means nothing to those who have to enter the data into that form, but the description would be "Gender, 0=male, 1=female", which is what they need to know. I'd much rather have the description appear as the label on my form, rather than the field name. Is there to do this automatically, without having to manually change the label of each field on my form?

Or better yet both - for example, if my label could include the field name followed by the description, that would be even better: "I_A - Gender, 0=male, 1=female"

Thanks!

 

[arnelgp]

you need to add code to the Form's Load Event.

MAKE sure you did not rename the Bound Field to
anything, like if you have ProductCode field,
you dont rename them to txtProductCode on the form.
If you rename them, make sure its consistent.
(prefix txt + FieldName). so you need to change
the way you call the function, ie:

getDescriptiveText("yourTAbleName", ctl.Name, "txt")

Private Sub Form_Load()
' arnelgp
'
' REPLACE "Orders" in this Event with the name of your table.
'
    Const TABLE_NAME As String = "Orders"
    
    Dim ctl As Control
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    For Each ctl In Me.Detail.Controls
        If TypeOf ctl Is TextBox Or TypeOf ctl Is ComboBox Then
                Debug.Print ctl.Properties("LabelName")
                Me(ctl.Properties("LabelName")).Caption = getDescriptiveText("Orders", ctl.Name)
        End If
    Next
End Sub

Private Function getDescriptiveText(ByVal Table As String, ByVal FieldName As String, Optional ByVal Prefix = "") As String
'
' arnelgp
'
' if you rename your bound textbox to "txt" + FieldName,
' specify the Optional parameter in this function (eg):
'
' getDescriptiveText("Orders", ctl.Name, "txt")
'
    Dim rs As DAO.Recordset
    Dim fld As DAO.Field
    Set rs = CurrentDb.OpenRecordset(Table, dbOpenSnapshot, dbReadOnly)
    For Each fld In rs.Fields
        If Prefix & fld.Name = FieldName Then
            getDescriptiveText = fld.Properties("Description")
            Exit For
        End If
    Next
    rs.Close
    Set rs = Nothing
    Set fld = Nothing
End Function

 

[HGCanada]

Thanks so much, Arnelgp!

 

[arnelgp]

there is still yet room for improvement.

remove this one on the load event:

Dim rs As DAO.recordSet
Set rs = Me.RecordsetClone


on getDescriptiveText() function, replace

Set rs = CurrentDb.OpenRecordset(Table, dbOpenSnapshot, dbReadOnly)

with

Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & Table & " WHERE (1=0);", dbOpenSnapshot, dbReadOnly)

since we only need the fields of the table and not it's records.


if you need to use this functionality to many forms,
better yet put the code in a Standard Module, then
call the Sub on each Form's Load Event:

Private Sub Form_Load()
Call ChangeTextLabelToDescript(Me, "yourTableNameHere")
End Sub

copy and paste in standard module

Public Sub ChangeTextLabelToDescript(ByRef ThisForm As Form, ByVal Table As String)
' arnelgp
'
    Dim ctl As control
    For Each ctl In ThisForm.Detail.Controls
        If TypeOf ctl Is textbox Or TypeOf ctl Is ComboBox Then
                'Debug.Print ctl.Properties("LabelName")
                ThisForm(ctl.Properties("LabelName")).Caption = getDescriptiveText(Table, ctl.Name)
        End If
    Next
End Sub

Public Function getDescriptiveText(ByVal Table As String, ByVal FieldName As String, Optional ByVal Prefix = "") As String
'
' arnelgp
'
' if you rename your bound textbox to "txt" + FieldName,
' specify the Optional parameter in this function (eg):
'
' getDescriptiveText("Orders", ctl.Name, "txt")
'
    Dim rs As DAO.recordSet
    Dim fld As DAO.Field
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & Table & " WHERE (1=0);", dbOpenSnapshot, dbReadOnly)
    For Each fld In rs.Fields
        If Prefix & fld.Name = FieldName Then
            getDescriptiveText = fld.Properties("Description") & ""
            Exit For
        End If
    Next
    rs.Close
    Set rs = Nothing
    Set fld = Nothing
End Function

 

[moke123]

Couldnt you just use the caption property of the field in the table?