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

Answer 2

I have a form that will show certain fields after a list box value is selected. I use the AfterUpdate function. It has worked so far. My code is below. ProjectName and ProjectNumber are fields you only want displayed if Engineering is selected. OtherName and OtherNumber are fields you only want to show if it is a "NotEngineering" project.

Insert this code by clicking on the Field that selects the project type, go to the Event tab on the property sheet, and click "After Update" and choose code builder and paste in VBA.

Private Sub ProjectType_AfterUpdate()
If ProjectType.Value = "Engineering" Then
      Me.ProjectName.Visible = True
      Me.ProjectNumber.Visible = True
 Else
      Me.ProjectName.Visible = False
      Me.ProjectNumber.Visible = False
 End If

If ProjectType.Value = "NotEngineering" Then
      Me.OtherName.Visible = True
      Me.OtherNumber.Visible = True
 Else
      Me.OtherName.Visible = False
      Me.OtherNumber.Visible = False
 End If

End Sub