VBA to highlight missing fields in a form

[nick404]

All,



Looking for some help on how to highlight empty fields when the user attempts an incomplete submission. I have done some googling around and found http://www.allenbrowne.com/highlight.html to be helpful, however he highlights the 'required' fields right off the bat and I only want to highlight empty fields after an attempted submission.

[orange]

You could try to put your code in the before update event of the Form.
This would be the last event before a record is updated. The value of each control would be present/not present and you could check to see if a valid value or any value was present.
Some posters use the Tag property of each Control--assign "required". Then in the Form's before update, iterate the controls checking the Tag value. If, for each required Tag, a value exists, then proceed to next Tag. If a control's Tag is "required", yet control has no value, you could set the background color accordingly. But any value versus an acceptable value (validation) will require more logic than above.

There will be samples in forums or on the internet.

But what is wrong with showing required controls/fields when form is loaded?

See the dialog and code at this link for more info and some examples.

You may also get ideas from this site.

[nick404]

Appreciate the guidance, orange!

[orange]

Happy to help. Good luck with your project.

[ItsMe]

I do something similar to this.
https://www.accessforums.net/forms/q...tml#post219951

[nick404]

ItsMe, I tried something similar to yours but it doesn't seem to do what I want. It stops the save sequence and brings up the error message, but it only highlights the very first textbox of my form, it doesn't seem to continue onward through the rest of the blank control.s

Dim c As Control
For Each c In Me.Controls
    If c.Tag = "Check" Then
        If IsNull(c.Value) Then
            c.BackColor = &HD0D0FF
            MsgBox "Ensure all fields are filled before submission", vbOKOnly, "Missing Information"
        End If
    Exit Sub
    End If
Next c

[orange]

Try a
Debug.print c.name & " " & C.tag & " " & C.value
to make sure the code is being executed
right after this line

For Each c In Me.Controls

Just to make sure the event is firing and the code is executed.

[nick404]

I get an error msg "Object doesn't support this method or property"
Upon hovering over the c.Name it displays the name of a button I have in the forms header... not what I want
This button does not have the Tag 'Check' however...

When I place that line as shown below

Private Sub cmdenter_Click()
Dim c As Control
For Each c In Me.Controls
    If c.Tag = "Check" Then
    Debug.Print c.Name & " " & c.Tag & " " & c.Value

I get the correct control name and tag in the immediate window, but value displays nothing:
Debug.Print c.Name & " " & c.Tag & " " & c.Value
txtmovname Check

[ssanfu]

Instead of
If IsNull(c.Value) Then

you might try
If Len(Trim(c.Value & "")) = 0 Then

[orange]

Ok, that was air code, and I should have checked further before posting or mentioned untested air code.

Here is some code I have used to identify all controls on all forms for a different project. I have highlighted a few lines showing the use of for each ctl in Me.Controls and getting the control name and type. Sorry for any confusion with the "air code".

140       DoCmd.OpenForm strForm, acDesign
150       Set objActiveForm = Application.Screen.ActiveForm
160       For Each ctl In objActiveForm.Controls
170   .AddNew
180   !Form_Name = strForm
190           Select Case ctl.ControlType
              Case 119 ' acWebBrowser, Treeview, Calendar
200           !control_type = "Custom control"
210           Case acTabCtl
...


520           !control_type = "Page"
530           Case acPageBreak
540           !control_type = "PageBreak"
550           Case acOptionGroup 'some sort of frame???
560           !control_type = "Option Group"
570           End Select
580           Debug.Print " " & ctl.name & "  " & ctl.ControlType
590   !control_name = ctl.name
600   !control_type = !control_type & "  " & ctl.ControlType  '  used to associate the control with the acControl constant
610   .Update
              
620       Next ctl

And I just realized after posting that this was done within Design view of the form.

So let's find some real examples from others.

After posting:I found this list of FAQ's regarding looping Through Controls with sample code to check Tag

Also: Not all Controls have the same properties --just be aware.

[CJ_London]

Looking for some help on how to highlight empty fields when the user attempts an incomplete submission

just on a slightly different tack

For the controls which you require to be completed and assuming they are text or combo boxes, set the format property to ;;;[Red]"This field must be completed".

This will show the message (in red) for all controls where there is no entry (i.e. is null) to act as a visual prompt to the user - tho' they can still ignore it so you still need to check they have completed it before updating the record

[ItsMe]

Also, not every control has a .Value property. Or a .Name property, etc. You can use the constants to iterate certain control types or you can place various phrases in the tag property or both.


For instance, a tag property in one of my controls may be ...
Required_HideOnCustomer_HideOnClear

Then I would employ the Instr() function for various events.
If instr(ctl.Tag, "Required") then
'Do Something Here
end if


Here are some constants but usually the Tag property is enough for data validation.
'Constant Control
'acBoundObjectFrame Bound object frame
'acCheckBox Check box
'acComboBox Combo box
'acCommandButton Command button
'acCustomControl ActiveX (custom) control
'acImage Image
'acLabel Label
'acLine Line
'acListBox List box
'acObjectFrame Unbound object frame or Chart
'acOptionButton Option button
'acOptionGroup Option group
'acPage Page
'acPageBreak Page break
'acRectangle Rectangle
'acSubform SubForm / SubReport
'acTabCtl Tab
'acTextBox Text box
'acToggleButton Toggle button Toggle button
'

[nick404]

Thanks to all for the help, currently working on digesting all this information and testing different things.

One question though,
I am using an unbound form: What can I put at the beginning of my submit button code that will cause the BeforeUpdate event to run? BeforeUpdate is where I am currently planning on placing this 'control validation' code (unless someone has a better idea?).

[nick404]

Gleaning ideas from each post and link you all have posted throughout my strife, I have finally achieved my goal. Will mark as solved and for anyone curious here is the solution I have.

Option Compare Database
Option Explicit
Public gbl_cancel As Boolean

I put this in my BeforeUpdate:

Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
'   **check for missing detail**
For Each ctl In Me.Section("Detail").Controls
    Select Case ctl.Tag
        Case "Required;Check"
            If Len(Trim(ctl.Value & "")) = 0 Then
            ctl.BackColor = &HD0D0FF
            gbl_cancel = True
            Else
            gbl_cancel = False
            End If
    End Select
Next ctl
End Sub

And this behind my submit button:

Sub cmdenter_Click()
Dim ctl As Control
Dim tb As Object
Dim DkS as Integer
Set tb = CurrentDb.OpenRecordset("tbl_Main")
Call Form_BeforeUpdate(0)
If gbl_cancel = False Then
'  ...code to add new records to table...
ElseIf gbl_cancel = True Then
Exit Sub
End If
' ...
End Sub

Again thanks to all, very helpful!