Highlighting required fields on a form

[decco21]

Hey guys,



I'm having a bit of trouble with a particular feature for my database at work. I'm using Access 2013 but working in a 2003-2007 (.mdb) database as it needs to work on everyone else's computers (not that this should affect this particular issue).

I'm trying to get fields on a form to be highlighted when required data is not entered and the user attempts to progress to the next stage. Here's a picture of the form I'm working on. When the user clicks "Add Drawing" I would like all empty fields to be highlighted and a messagebox displayed to inform them they need to fill out the rest of the fields before the record can be added.



The issue is that while the code seems to work somewhat, it appears to highlight the control before it (or sometimes after it) and won't un-highlight itself once filled and the "Add Drawing" button is clicked again (by the way, the button is actually a label but I don't think that's an issue either). Here's a picture of the form with this problem.



NOTE: In the code below I have the final step commented out as I'm more interested in getting the rest working first.

Any help with my code would be greatly appreciated! Thanks in advance!

Private Sub AddButton_Click()


Dim ctl As Control


'------Reset all control formatting------


    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox
                With ctl
                    .BorderColor = Val("#A6A6A6")
                    .BorderWidth = Val("Hairline")
                    .BorderStyle = 1
                End With
        End Select
    Next ctl
    
'------Highlight controls if empty------
    
    For Each ctl In Me.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox
                If Len(Nz(ctl.Value, vbNullString)) = 0 Then
                    With ctl
                       .BorderColor = vbRed
                        .BorderWidth = 1
                        .BorderStyle = 1
                    End With
                End If
        End Select
    Next ctl
    MsgBox "Missing required fields." & vbCrLf & "Please fill in all emtpty fields and try again.", vbCritical, "Can't save drawing"
    
'------Add drawing if all controls filled------


   ' DoCmd.RunCommand acCmdSaveRecord
   ' Beep
   ' MsgBox "Drawing saved!", vbOKOnly, "Add a Drawing"


End Sub

[pbaldy]

I would think that would work, but perhaps having 2 loops is confusing things. I'd just loop controls once, and use an Else clause to set the format if not empty. Can you post the db here?

[pbaldy]

Oh, and I'd also use a variable to determine whether any fields failed validation. As you have it, you'll get the message box even if nothing is left blank.

[decco21]

Can you post the db here?

Hi pbaldy, thanks for your reply! A simplified version of my database is attached. I really appreciate your help on this.

As you have it, you'll get the message box even if nothing is left blank.

I was thinking that would happen, but hadn't really gotten that far yet!

[pbaldy]

Try this. Also, your issue with inconsistent application of the formatting is because you're using a label. Because a label can't receive focus, the focus stays on the last control, so it's value isn't necessarily committed yet. You can switch to a button or explicitly set focus somewhere at the start of the code, which will commit the value in the textbox/combo.

Dim ctl As Control
Dim booFail As Boolean

'------Reset all control formatting------
booFail = False

  For Each ctl In Me.Controls
    Select Case ctl.ControlType
      Case acTextBox, acComboBox
        With ctl
          If Len(Nz(.Value, vbNullString)) = 0 Then
            .BorderColor = vbRed
            .BorderWidth = 1
            .BorderStyle = 1
            booFail = True
          Else
            .BorderColor = Val("#A6A6A6")
            .BorderWidth = Val("Hairline")
            .BorderStyle = 1
          End If
        End With
    End Select
  Next ctl
  
  If booFail Then
    MsgBox "Missing required fields." & vbCrLf & "Please fill in all emtpty fields and try again.", vbCritical, "Can't save drawing"
  End If

[decco21]

...explicitly set focus somewhere at the start of the code...

You're a legend mate, that worked a treat. I don't have buttons as I really wanted them to be blue, and Access 2003 and older don't have the functionality to change the background colour of buttons, so thanks for the workaround!

Cheers for all your help!

[pbaldy]

Happy to help!