Recreate status bar text from table defintions? 1

[bjb123]

I'm trying change the status bar text on the bound controls of a bound form so that it matches updated descriptions in the underlying table.

There are too many controls for me to just re-drag and drop. Is there a way to loop through and change it programmatically?

 

[JoeAtWork]

Um, do you mean the labels next to the textboxes? If so, open the form in design mode and just click on the labels and start typing (or you can type in the Caption property).

 

[bjb123]

No, it's the Status Bar Text on the "Other" tab in the properties of textboxes. It's the text that shows up in the lower left hand corner of the screen when a control has focus.

When you create a new bound form, the value gets pulled automatically off the field description from the underlying table.

 

[TheAceMan1]

How are ya bjb123 . . .

You can use the [blue]Status Bar Text[/blue] property of the controls, however its not permanent unless its done in design view! Here's the secnario:
[ol][li]Open a [blue]TableDef[/blue] object on the table and open the form in design view.[/li]
[li]Looping thru the [blue]Fields Collection[/blue] of the TableDef you set the appropriate controls [blue]Status Bar Text[/blue] property in the form.[/li]
[li]SAVE and close the form . . . close the TableDef object![/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[Remou]

Some notes.

Sub UpdateControls()
Dim frm As Form
Dim ctl As Control
Dim rs As DAO.Recordset
Dim db As DAO.Database

Set db = CurrentDb
DoCmd.OpenForm "Table1", acDesign
Set frm = Forms!Table1

Set rs = db.OpenRecordset(frm.RecordSource)

For Each ctl In frm.Controls
    If ctl.ControlType = acTextBox _
    Or ctl.ControlType = acComboBox _
    Or ctl.ControlType = acListBox Then
    
        On Error Resume Next
        ctl.StatusBarText = rs(ctl.ControlSource).Properties("Description")
    
    End If
Next
End Sub

 

[TheAceMan1]

bjb123 . . .

A starting point:

[blue]Public Sub UpdateDescription(frmName As String, tblName As String)
   Dim db As DAO.Database, tdf As DAO.TableDef, frm As Form, fld As Field
   
   DoCmd.OpenForm frmName, acDesign
   Set db = CurrentDb
   Set tdf = db.TableDefs(tblName)
   Set frm = Forms(frmName)
   frm.Visible = False
   
   For Each fld In tdf.Fields
      frm(fld.Name).StatusBarText = fld.Properties("Description")
   Next
   
   DoCmd.Close acForm, frmName, acSaveYes
   Set frm = Nothing
   Set tdf = Nothing

End Sub[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[bjb123]

Thanks TheAceman1 and Remou. I had just found a module in The Access Cookbook that would have helped but Remou's code did the trick far more elegantly than what I would have coded.

TheAceman1, I wasn't able to get code to work. It got a runtime 13 error on the "For Each fld In tdf.Fields" line. a mouseover on fld variable showed: fld = nothing

Anyway, Remou's code handled it. Thanks again for your help!