Description of Table Field - MS Access

Question

I've looked around and found a few VBA codes on how to grab the description out of the field's "Description" box, but not how I can use that in a form's properties.

I'd like to have a ControlTip appear with the Description of that field brought from the Description in the database, without having to rewrite all the descriptions; I'm hoping for a copy-paste bit of code that I can add to all controltips?

Something like (but obviously not)

ControlTipText:  Me.ThisControl.ThisControlFieldDescription

Anybody know of the code, or even if there is one?

EDIT:

description = Forms("frmTrials").Controls("txtBox").StatusBarText
MsgBox description

The above works to show the status bar text. However I'd like to fill "frmTrials" with the active form and "txtBox" with the currently active control; that way when the control becomes active I can put the StatusBarText into a "Description Box" text field (or the control tip, etc). I tried

description = Forms(Me).Controls(Me.ActiveControl).StatusBarText

which just threw errors at me.

Answer 1

As I understand the situation, you want to set ControlTipText properties dynamically each time your form loads. Since you indicated in a comment that this application is intended for a tablet device, you might prefer to limit the processor load when opening your form. You could do that by saving the ControlTipText properties with the form's design.

Try the following procedure with your form's name like this:

SetControlTipText "YourFormName"

Here is the procedure. I didn't find any problems in limited testing. It sets ControlTipText for check boxes, combos, list boxes, and text boxes. Change the first Case line to target a different set of controls.

Public Sub SetControlTipText(ByVal pFormName As String)
    Dim ctl As Control
    Dim db As DAO.Database
    Dim frm As Form
    Dim rs As DAO.Recordset

    DoCmd.OpenForm pFormName, acDesign
    Set frm = Forms(pFormName)
    If Len(frm.RecordSource) > 0 Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset(frm.RecordSource)
        For Each ctl In frm.Controls
            Select Case ctl.ControlType
            Case acCheckBox, acComboBox, acListBox, acTextBox
                If Len(ctl.ControlSource) > 0 _
                        And Not ctl.ControlSource Like "=*" Then
                    ctl.ControlTipText = _
                        GetDescription(rs.Fields(ctl.ControlSource))
                End If
            Case Else
                ' pass '
            End Select
        Next ctl
        rs.Close
    End If
    Set ctl = Nothing
    Set rs = Nothing
    Set db = Nothing
    Set frm = Nothing
    DoCmd.Close acForm, pFormName, acSaveYes
End Sub

SetControlTipText calls this function:

Public Function GetDescription(ByRef pObject As Object) As String
    Dim strReturn As String

On Error GoTo ErrorHandler

    strReturn = pObject.Properties("Description")

ExitHere:
    GetDescription = strReturn
    On Error GoTo 0
    Exit Function

ErrorHandler:
    strReturn = vbNullString ' make it explicit '
    GoTo ExitHere
End Function

That SetControlTipText procedure ignores unbound forms. If the control source for a bound field doesn't have a Description property assigned, its ControlTipText will be set to an empty string.

This approach will require you to run the procedure once for the form, rather than running some other procedure each time the form loads. If you later change Description properties for any of the form's record source fields, you can re-run SetControlTipText to update ControlTipText 's.

Or you could run the procedure for all your application's forms as part of your preparations to release a new version of the application.

Dim frm As Object
For Each frm in CurrentProject.AllForms
    SetControlTipText frm.Name
Next frm

Answer 2

You can try a variation of this to go through all the controls on the form and set the their Tooltip to whatever field matches the bound data source.

Private Sub Form_Load()
    ' Load tooltips for the current form '
    ' Place this in all subforms as well '
    SetToolTips Me
    ' If the form is bound at runtime, you can call use instead '
    SetToolTips Me, myDataRecordSet
End Sub

Private Sub SetToolTips(frm As Form, Optional rs As dao.Recordset)
    Dim ctls As Controls
    Dim ctl As Control
    Dim sourceField As String
    Dim description As String

    On Error Resume Next

    Set ctls = frm.Controls
    If rs Is Nothing Then Set rs = frm.Recordset

    For Each ctl In ctls
        sourceField = ctl.ControlSource
        If Len(sourceField) > 0 Then
            description = rs.Fields(sourceField).Properties("Description")
            If Len(description) > 0 Then
                ctl.ControlTipText = description
            End If
        End If
    Next ctl
    Set ctls = Nothing
End Sub

Answer 3

Ended up using a text field to show the descriptions instead of the control tip. I also have an informative photo appear (if there is one so named in the given folder). I should note that I do not have any handling in place for images which are not PNG format, though I'm sure that could be added.

Public Function pushInfo(frm As Form)
'On Error Resume Next
Dim desc As String 'description from the status bar of the active control
Dim path As String 'path to image
Dim dbPath As String 'path to the database
Dim hyperPath As String 'path to hyperlink

'Take the statusbar text and push it into the description box caption.
desc = Forms(frm.Name).Controls(frm.ActiveControl.Name).StatusBarText 'Put statusbar text into var "desc"
frm.txtInfo.Caption = vbNewLine & vbNewLine & desc 'Put the text (with linefeeds) into the box
frm.lblInfo.Caption = frm.ActiveControl.Name & " Description:" 'Put the database name of the field into the label

'Set the image in the imgbox
dbPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))   'path to the DB.
path = dbPath & "img\" 'add the img directory
path = path & frm.Name & "\" 'add the form name
path = path & frm.ActiveControl.Name 'add the control's name
path = path & ".png" 'add the jpg suffix
hyperPath = path

If (Len(Dir(path)) = 0) Then 'if the image doesn't exist (this field has no image..)
    path = dbPath & "img\GenericLogo.png" 'set to the logo
    hyperPath = ""
End If

Forms(frm.Name).Controls("imgInfo").Picture = path 'set the picture to the defined path
Forms(frm.Name).Controls("imgInfo").HyperlinkAddress = hyperPath 'set the picture to link to the file
End Function