Writing code for multiple boxes in VBA

[omarrr128]

I have code which currently reads something like:

If Me.Priority = "Urgent" Then
Me.command200.visible = false
Me.command201.visible = false
Me.command202.visible = false
End if

Is it possible to group the command boxes together under a bracket and then have then all = false rather than write them all out individually?


Also I have some code to write for the form.footer to be visible conditionally.

If me.recordset.recordcount < 1 then
Me.formfooter.visible = true
End if

Under what form event would I write this code? I've tried a few and they don't work
(For example form_current)

Thank you very much

 

[theDBguy]

If the names have numerical suffixes, you could use a loop. The Current event sounds good to me.

 

[June7]

Is it possible to group the command boxes together under a bracket and then have then all = false rather than write them all out individually?

Short answer is no and "under a bracket" really doesn't mean anything. However, can do a loop like:

For x = 0 to 2
    Me.Controls("command20" & x).Visible = Not Me.Priority = "Urgent"
Next

Current event sounds reasonable.

 

[MarkK]

...rather than write them all out individually?

    Dim i As Integer
  
    For i = 0 To 2
        Me.Controls("command20" & i).Visible = Not Me.Priority = "Urgent"
    Next

...or maybe...

    Const BUTTONS_TO_HIDE As String = "cmdOK cmdCancel cmdMoreOptions"
    Dim var
    
    For Each var In Split(BUTTONS_TO_HIDE)
        Me.Controls(var).Visible = Not Me.Priority = "Urgent"
    Next

Under what form event would I write this code? (For example form_current?)

Does anything go wrong when you use the current event?

 

[omarrr128]

    Dim i As Integer
 
    For i = 0 To 2
        Me.Controls("command20" & i).Visible = Not Me.Priority = "Urgent"
    Next

...or maybe...

    Const BUTTONS_TO_HIDE As String = "cmdOK cmdCancel cmdMoreOptions"
    Dim var
   
    For Each var In Split(BUTTONS_TO_HIDE)
        Me.Controls(var).Visible = Not Me.Priority = "Urgent"
    Next

Does anything go wrong when you use the current event?

Thank you for first half.

Current doesn't work. Nothing happens.
My code is


If me.recordset.recordcount < 1 then
Me.formfooter.visible = true
End if

I've tried putting it under a few form events and none seem to work.

 

[MarkK]

How do we know Me.Recordset.RecordCount is zero? Also, unless your form is in DataEntry mode, when the recordset has no rows, nothing on the form is visible anyway. And if everything is hidden, because there are no rows, then you will not see the footer, even if it is visible.

 

[omarrr128]

How do we know Me.Recordset.RecordCount is zero? Also, unless your form is in DataEntry mode, when the recordset has no rows, nothing on the form is visible anyway. And if everything is hidden, because there are no rows, then you will not see the footer, even if it is visible.

I tried it under the form timer event which requerys the form every minute and it worked perfectly there.

I do not want it there tho as it would work when the form refreshes every minute.
I need it somewhere that it would work instantly when the recordcount changes

 

[MarkK]

But you control the Recordset. You open it, and you either add records, or delete records. At those points, where you make those changes, test your recordcount and show/hide your footer. Form events to check out: Open, Load, AfterInsert, AfterDelConfirm.

 

[moke123]

If Me.Priority = "Urgent" Then
Me.command200.visible = false
Me.command201.visible = false
Me.command202.visible = false
End if

Is it possible to group the command boxes together under a bracket and then have then all = false rather than write them all out individually?

You can also use the tag property and loop through the forms controls

Dim ctl as Control

For each ctl in Me.Controls
     If instr(1,ctl.tag,"Urg") then
          ctl.visible = False
     End if
next

 

[Pat Hartman]

Is it possible to group the command boxes together under a bracket and then have then all = false rather than write them all out individually?

No. VBA does not support that construct. I would use the tag solution suggested by Moke. If you can't use the Tag method, you could name the controls with a numeric suffix that is calculated in a loop. This lets you "calculate" the control name.

 

[CJ_London]

You can write this
If me.recordset.recordcount < 1 then
Me.formfooter.visible = true
End if

as

Me.formfooter.visible =me.recordset.recordcount < 1

 

[Uncle Gizmo]

Is it possible to group the command boxes together under a bracket

The best way is to loop through the set of controls, interrogating something different about the control like a flag in the tag setting. Alternatively you can place an identifier in each control name:- btnXbuttonName1, btnXbuttonName2.... Etc

I came up with a graphical way of handling sets of controls which you can see described in this YouTube video here:-