Table Field Description - MS Access

Question

I looked around and found some VBA codes on how to capture the description from the Description field, but not how I can use it in the form properties.

I would like for the ControlTip element to appear using the description of this field that appeared from the description in the database, without having to rewrite all the descriptions; I hope for a copy-paste bitcode that I can add to all the telltale hints?

Something like (but obviously not)

ControlTipText: Me.ThisControl.ThisControlFieldDescription 

Does anyone know the code, or even if it is?

EDIT:

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

The above works to show the status bar text. However, I would like to populate frmTrials with the active form and txtBox with the currently active control; that way, when the control becomes active, I can put the StatusBarText in the Description text box (or control tip, etc.). I tried

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

who just threw mistakes at me.

Answer 1

Finished with a text box to display descriptions, not tooltips. I also have an informative photo (if one name is so named in this folder). I should note that I do not have any processing for images that are not a PNG format, although I am sure that they 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 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 

Answer 2

As I understand it, you want to dynamically set the ControlTipText properties every time your form loads. Since you indicated in the commentary that this application is intended for a tablet device, you can limit the processor load when opening the form. You can do this by saving the ControlTipText properties using the form design.

Try the following procedure with your form name as follows:

 SetControlTipText "YourFormName" 

Here is the procedure. I did not find any problems in limited testing. It sets a ControlTipText for checkboxes, combos, lists, and text fields. Change the first line of the Case to configure 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 

This SetControlTipText procedure ignores unrelated forms. If the control source for the linked field does not have an assigned Description property, its ControlTipText will be set to an empty string.

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

Or you can start the procedure for all your application forms in preparation for the release of a new version of the application.

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

Answer 3

You can try a variant of this to go through all the controls on the form and set their tooltip in any field that matches the associated 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