CODEWITHSUNDEEP
ms-access
Michael Amos
Michael Amos

Reputation: 59

Forms/ VBA - activating controls based on status

This question isn't particularly technical, it's more theoretical.

I'm doing the "project queue" thing in Access that I think we all do from time to time. We've got several steps to go through. I'm hiding buttons that don't make sense in a given step and adding a star to the most relevant tab. For example, if I have been assigned development of a project, the "move to production" button is hidden and disabled because I need to go through testing before I can move the project to production. I also rename the development tab to Development* to queue me to that.

Everything was hardcoded in VBA and it still can be but it's getting longer and uglier to maintain. I'm wondering if someone has a best practice or just general suggestion. I had 6 statuses but I'm going to 12 and feel like it's time to think about doing this better.

When a button is clicked you get a code block like this:

Private Sub AssignScoping_Click()
  Me.RequestStatus.Enabled = True
  Me.RequestStatus = "Scoping"
  Me.RequestStatus.Enabled = False
End Sub

Each button just assigns a different text value to the RequestStatus field which drives the rest of the logic which looks like this:

Private Sub setButtonAvailability()

Select Case Me.RequestStatus
  Case Null
    Me.PlaceInQueue.Visible = True
    Me.PlaceInQueue.Enabled = True
    Me.AssignScoping.Visible = False
    Me.AssignScoping.Enabled = False
    Me.AssignDevelopment.Visible = False
    Me.AssignDevelopment.Enabled = False
    Me.AssignTesting.Visible = False
    Me.AssignTesting.Enabled = False
    Me.AssignProduction.Visible = False
    Me.AssignProduction.Enabled = False
    Me.AssignAutomation.Visible = False
    Me.AssignAutomation.Enabled = False

    Me.Tabs.Pages("Intake").Caption = "Intake" & "*"
    Me.Tabs.Pages("Scoping").Caption = "Scoping"
    Me.Tabs.Pages("Development").Caption = "Development"
    Me.Tabs.Pages("Testing").Caption = "Testing"
    Me.Tabs.Pages("Production").Caption = "Production"
    Me.Tabs.Pages("Automation").Caption = "Automation"

 ...


  Case Else
    Me.PlaceInQueue.Visible = True
    Me.PlaceInQueue.Enabled = True
    Me.AssignScoping.Visible = True
    Me.AssignScoping.Enabled = True
    Me.AssignDevelopment.Visible = True
    Me.AssignDevelopment.Enabled = True
    Me.AssignTesting.Visible = True
    Me.AssignTesting.Enabled = True
    Me.AssignProduction.Visible = True
    Me.AssignProduction.Enabled = True
    Me.AssignAutomation.Visible = True
    Me.AssignAutomation.Enabled = True

    Me.Tabs.Pages("Intake").Caption = "Intake"
    Me.Tabs.Pages("Scoping").Caption = "Scoping"
    Me.Tabs.Pages("Development").Caption = "Development"
    Me.Tabs.Pages("Testing").Caption = "Testing"
    Me.Tabs.Pages("Production").Caption = "Production"
    Me.Tabs.Pages("Automation").Caption = "Automation"

  End Select
End Sub

I figure there are a ton of options including putting some of the controlling info in a table for just that purpose but thought I'd bounce it off your collective noggins for suggestions as I tend to work in isolation at my job and I don't always think of the best way to do something, just the way I can do it right now.

Upvotes: 0

Views: 123

Answers (1)

June7
June7

Reputation: 21379

Textbox and combobox can employ Conditional Formatting rules to set enabled/disabled state.

Doesn't matter what approach is taken, still have to 'touch' each control to set its property. One approach is to generically loop through controls collection without having to explicitly reference each control by name and set the Enabled or Visible properties according to some criteria. Use of control's Tag property might be helpful. Example:

Dim ctl As Control
For Each ctl In Me.Controls
    If ctl.ControlType = acCommandButton Then
        ctl.Visible = ctl.Tag = Me.RequestStatus
    End If
Next ctl

Another approach is to give controls similar name, like btnProj1, btnProj2, etc. Then loop could be limited to that set of controls:

For x = 1 to 10
    Me.Controls("btnProj" & x).Visible = Me.Controls("btnProj" & x).Tag = Me.RequestStatus
Next

If you set a control not visible, why bother with the Enabled property?

Upvotes: 1

Related Questions