Blanking out records but when combo box option selected make filling them out compulsory

[PrettyMess]

I'm hoping someone may be able to help with this I have managed to get the basic's but I'm a bit stuck with the next stage.

Currently I have been able to grey out the required fields when the various options are selected from my combo box "Type" I have also been able to get the code to check my fields to make sure that something is entered into the non greying out fields.

However the part I am stuck on is that when my 'Type' drop down is selected I need to make the non greyed out boxes compulsory.

The user must select one of the 4 options in the 'type' box (so i need to make sure they have selected one or they get prompted to pick one) then if the user selects the option for voice in the type field all boxes become white and I need the user to fill them all in (with no gaps) so I want to make sure it all gets filled in or else they get prompted too go back and fill in. where as if the user selects Alarm they don't need to complete any of the information on the right hand side so it gets greyed out and they are able to proceed onto the next record

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(TypeList.Value) Then
  MsgBox "You Must Enter a Type"
  Cancel = True
  TypeList.SetFocus
  Exit Sub
End If

If Me.DDI.Enabled = True Then
If Me.txtFldA.Text = "" Then
If IsNull(DDI.Value) Then
  MsgBox "You Must Enter a Number in DDI"
  Cancel = True
  DDI.SetFocus
  Exit Sub
End If

If IsNull(Extn.Value) Then
  MsgBox "You Must Enter a Number in Extn"
  Cancel = True
  Extn.SetFocus
  Exit Sub
End If

If IsNull(Location.Value) Then
  MsgBox "You Must Enter a Location"
  Cancel = True
  Location.SetFocus
  Exit Sub
End If

If IsNull(Analogue.Value) Then
  MsgBox "You Must Make a selection Analogue/Cisco"
  Cancel = True
  Analogue.SetFocus
  Exit Sub
End If
 
If IsNull(StaffNumber.Value) Then
  MsgBox "You Must Include a staff number"
  Cancel = True
  StaffNumber.SetFocus
  Exit Sub
End If

 If IsNull(Firstname.Value) Then
  MsgBox "You Must Include a Firstname"
  Cancel = True
  Firstname.SetFocus
  Exit Sub
End If

 If IsNull(Surname.Value) Then
  MsgBox "You Must Include a Surname"
  Cancel = True
  Surname.SetFocus
  Exit Sub
End If

 If IsNull(UserCode.Value) Then
  MsgBox "You Must Include a Username"
  Cancel = True
  UserCode.SetFocus
  Exit Sub
End If

If IsNull(DeptCode.Value) Then
  MsgBox "You Must Include a Department"
  Cancel = True
  DeptCode.SetFocus
  Exit Sub
End If

Select Case Me.TypeList.Value

Case Is = "Voice"
Me.StaffNumber.Enabled = True
Me.Firstname.Enabled = True
Me.Surname.Enabled = True
Me.UserCode.Enabled = True
Me.DeptCode.Enabled = True
Me.FaxLine.Enabled = False

Case Is = "Fax"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = True

Case Is = "Alarm"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = False

Case Is = "Lift"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = False

Case Is = "Hunt Group"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = False

End Select

End Sub

Private Sub TypeList_AfterUpdate()

Select Case Me.TypeList.Value

Case Is = "Voice"
Me.StaffNumber.Enabled = True
Me.Firstname.Enabled = True
Me.Surname.Enabled = True
Me.UserCode.Enabled = True
Me.DeptCode.Enabled = True
Me.FaxLine.Enabled = False

Case Is = "Fax"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = True

Case Is = "Alarm"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = False

Case Is = "Lift"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = False

Case Is = "Hunt Group"
Me.StaffNumber.Enabled = False
Me.Firstname.Enabled = False
Me.Surname.Enabled = False
Me.UserCode.Enabled = False
Me.DeptCode.Enabled = False
Me.FaxLine.Enabled = False

End Select

End Sub

 

[bugmonsta]

What about checking for text boxes/combo boxes for colours? I use this in EXCEL so I don't know if it would work in Access??

In my example below, I have a userform with some textboxes and combo boxes, which down to validation are set to be light yellow to show they are mandatory.

If the user presses my save button it runs this bit of code, which looks for any yellow fields. (on my fields I could have it set to remove the colour once they are filled in)

Dim ctl, ctl2, ctl3 As Control
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "ComboBox" Or TypeName(ctl) = "TextBox" Then
            If ctl.BackColor = RGB(255, 255, 153) Then '''' You can also search for colours such as your non greyed ones
            MsgBox "YOU HAVE FIELDS THAT ARE NEEDED - This is the missing field name: " & ctl.Name 'you don't need the control name but I added it to show it could be done!
             Exit Sub
                End If
                End If
                Next ctl
                'your other code
                Unload Me

In my real file I set certain fields to have the word required in them. Then on loading the form it checks the combo/text boxes for this value and colours them light yellow.

If the user tries to save the record, the system looks through all my fields and changes the colour of the box and text them (one at a time) to highlight which text boxes/combo boxes they should be looking at!

' this is another example - it looks for a value which I have set as default

Dim dt As String ' these values are set elsewhere but I have put them here to show you!
dt = "required"
Dim ctl  As Control
    For Each ctl In UserForm1.Controls
        If TypeName(ctl) = "ComboBox" Or TypeName(ctl) = "TextBox" Then
            If ctl.Value = dt Then
            MsgBox "YOU HAVE FIELDS THAT ARE NEEDED - FILL BABY FILL!"
                ctl.BackColor = RGB(255, 120, 153) ' colours the fields!!
                ctl.ForeColor = vbRed 'sets the text to red
                Exit Sub
                End If
                End If
                Next ctl
                Unload Me

For anyone wanting to test this, I did this:

Create a userform (leave it named userform1 for this test), create a textbox and call it TEST_TextBox, create a commandbutton.

Use the code below against the userform loading, this colours the textbox and puts the word required into it:

Private Sub UserForm_Initialize()
TEST_TextBox.BackColor = RGB(255, 255, 153)
TEST_TextBox.Value = "required"
End Sub

Assign any of the top 2 bits of code against the command button and run the form - click the button to see what it does.