Make fields visible in MS Access form as certain f

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

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

Answer 2

There is a way to do not-quite-this without VBA. I'd definitely recommend VBA though, because you can do a lot more with it.

Rather than hiding, try disabling the unnecessary fields with conditional formatting by expression.

right-click on the control you want disabled. go down and click on 'Conditional Formatting' Add a new rule Select 'Expression is'

example: [fld1]="yes"

hit the disabled box click ok click ok

now the control you've selected will disable if field 1 has "yes" selected.

Answer 3

I have the same problem and I did the following:

Private Sub Field1_Click()
If Field1 = "Yes" Then
Me.Field2.Visible = True
Else: Me.Field2.Visible = False
End If
End Sub

but now I have other problem, when I change record, the field that I choosen to be visible in the last record is now visible on the current record, although I have not choosen any option.

Thank you,

Answer 4

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