CODEWITHSUNDEEP
vbams-access
Sylvain
Sylvain

Reputation: 1

Showing/Hiding fields in form on result of drop down menu

I am building a form that takes data a query on three access separate tables.

The problem I have it that my previous version of the for had too many fields and thus was confusing for the end user. I have a drop down menu with four distinct options. Those are the only four that can be chosen (so as to prevent random data entry) and depending on which gets selected, I want to have some fields show and other not, since they would not be relevant to it. I'm also wanting this to work when a user browses through the records of the form (i.e.: the fields show/hide depending on the value in the drop down menu).

I've tried to use the following code (I'm a complete beginner in VBA):

Private Sub Status_Change()
    If Status.Value = "Blue" Then
        Me.Field_1.Visible = False
        Me.Field_2.Visible = False
        Me.Field_3.Visible = False
        Me.Field_4.Visible = False
        Me.Field_5.Visible = False
        Me.Field_6.Visible = False
        Me.Field_7.Visible = False
    Else
    If Status.Value = "Green" Then
        Me.Field_1.Visible = True
        Me.Field_2.Visible = False
        Me.Field_3.Visible = False
        Me.Field_4.Visible = False
        Me.Field_5.Visible = False
        Me.Field_6.Visible = True
        Me.Field_7.Visible = True
    Else
    If Status.Value = "Red" Then
        Me.Field_1.Visible = True
        Me.Field_2.Visible = False
        Me.Field_3.Visible = False
        Me.Field_4.Visible = False
        Me.Field_5.Visible = False
        Me.Field_6.Visible = True
        Me.Field_7.Visible = True
    Else
    If Status.Value = "Yellow" Then
        Me.Field_1.Visible = True
        Me.Field_2.Visible = True
        Me.Field_3.Visible = True
        Me.Field_4.Visible = True
        Me.Field_5.Visible = True
        Me.Field_6.Visible = False
        Me.Field_7.Visible = True
    Else
    If Status.Value = "Orange" Then
        Me.Field_1.Visible = True
        Me.Field_2.Visible = False
        Me.Field_3.Visible = False
        Me.Field_4.Visible = False
        Me.Field_5.Visible = False
        Me.Field_6.Visible = False
        Me.Field_7.Visible = True
    End If
End Sub

So far it has not worked and I am not really sure on what I am doing wrong. Is anyone able to help me?

Thanks in advance.

Edit: The fields stay visible, no matter what the "Status" value shows when browsing through the records or inputting a new record. There are no errors given.

Upvotes: 0

Views: 237

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

I would suggest using the AfterUpdate event rather than the OnChange event.

Assuming Status is a combobox, you may want to check that the bound column of the combobox holds the values you are testing for.

Note that your code could be reduced to the following:

Private Sub Status_AfterUpdate()
    Me.Field_1.Visible = Status <> "Blue"
    Me.Field_2.Visible = Status = "Yellow"
    Me.Field_3.Visible = Status = "Yellow"
    Me.Field_4.Visible = Status = "Yellow"
    Me.Field_5.Visible = Status = "Yellow"
    Me.Field_6.Visible = Status = "Green" or Status = "Red"
    Me.Field_7.Visible = Status <> "Blue"
End Sub

Upvotes: 1

Related Questions