Make fields visible in MS Access form as certain fields are completed

Question

I am building a form In MS Access for users to input data but there are too many possible fields. Most of the time only about half the fields will be used.

I thus would like to have certain fields appear only depending on what the user inputted on a prior given field.

Ex: user enters project number, title, then he checks a "yes/no" engineering. since he checked it this means engineering is impacted so a dozen fields that the user will have to fill out appear.

Is this possible:

1)without VBA

2)with VBA

Answer 1

Probably not possible without VBA.

With VBA for example:

1. Ensure your form is in Design view
2. Right click on your Combo Box, Build Event, Code Builder

This opens the code behind your form. It drops you into the default code for the BeforeUpdate event. We want the Change event instead, so at the top right change the drop down from BeforeUpdate to Change. This will give you a bit of code like this:

Private Sub Field1_Change()

End Sub

Inside here, you want to check the value of the combo box and hide fields as required:

Assuming the name of your combo box is Field1 (yours of course will be different), you add some code so it looks like this to hide Field2:

Private Sub Field1_Change()
  If Field1.Value = "Yes" Then
      Me.Field2.Visible = False
  End If
End Sub

Note you need to know the names of all your fields - this is in the Other tab, Name field in the properties box (F4). You should give all of your fields sensible names so you can understand what is going on in the code.

For a check box, follow exactly the same procedure, but you probably need to use the Click event. Just experiment.

Sample check box code:

Private Sub Check5_Click()
  ' Note: vbTrue = -1
  If Me.Check5 = vbTrue Then
    MsgBox ("Ticked")
  Else
MsgBox ("Not Ticked")
  End If
End Sub