button captions (1 Viewer)

[John Sh]

I have a button with the caption set as "Herbarium Collection" in the property sheet.
I programmatically reset the caption to something different but later want it set back to the property sheet value.
So when the button is disabled I want the caption to be "Herbarium Collection"
There are 5 buttons on the form and the captions depend on previous search results.
Is this possible without closing and reopening the form?

Private Function setButtons(sSearch As String)
    Dim db As Database
    Dim rs1 As Recordset
    Dim sButton As String
    Dim nQuantity As Integer
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("listTables", dbOpenSnapshot)
    With rs1
        .MoveFirst
        Do
            If !TableName = "Barry collier collection" Or !TableName = "Duplicates" Then
                .MoveNext
            End If
            sButton = !ButtonName
            nQuantity = !quantity
            If !quantity = 0 Then
                Me.Controls(sButton).Enabled = False
                Me.Controls(sButton).Caption = property sheet value
            Else
                Me.Controls(sButton).Enabled = True
                Me.Controls(sButton).caption = Me.Controls(sButton).caption & vbCrLf & "Has " & nQuantity & " records with no " & sSearch & " data."
            End If
            .MoveNext
        Loop While Not .EOF
    End With
End Function

 

[arnelgp]

since you have the Button Name saved to a table, you can add a Field on that table for the Default Caption (defCaption, string).
the on your If..Then...Else code (If !Quantity...) you can use the defCaption for your buttons caption:

With Me(sButtons)
.Enabled = False
.Caption = rs1!defCaption & ""
End With

 

[MarkK]

In Form_Load or Form_Open you could store the original button caption in the buttons .Tag property, and then revert to it that way. Here's how I might approach it in code...

Private Sub Form_Load()
    Dim btn As Access.CommandButton
    
    For Each btn In Array(Me.button1, Me.button2, Me.button3, Me.button4, Me.button5)
        btn.Tag = btn.caption
    Next
End Sub

Private Sub setButtons(sSearch As String)
    Dim btn As Access.CommandButton
    
    With CurrentDb.OpenRecordset("listTables")
        Do While Not .EOF
            If !TableName = "Barry collier collection" Or !TableName = "Duplicates" Then .MoveNext
            If Not .EOF Then
                Set btn = Me.Controls(!ButtonName)
                If !Quantity = 0 Then
                    SetButton btn, False, btn.Tag
                Else
                    SetButton btn, True, btn.caption & vbCrLf & "Has " & !Quantity & " records with no " & sSearch & " data."
                End If
                .MoveNext
            End If
        Loop
    End With
End Sub

Private Sub SetButton(btn As Access.CommandButton, state As Boolean, caption As String)
    btn.Enabled = state
    btn.caption = caption
End Sub

 

[MarkK]

Arnel offers a good idea too! Save it in the table.

 

[John Sh]

Thank you both.
Two simple ideas and both easy to implement. I will probably go with Arnel's method but will definitely storeMark's method away for future reference.
John