skipping controls on validation check before update

[cbkebasen]

Hello everyone. Newby VBA learner here. I'm working on a code that checks all blank/null text fields and combo boxes in a form and notifies the user if a field is not filled. The code below works if all fields are required, but I can't use it on forms that have fields that are not really mandatory. I've read somewhere on the net to use tags on the controls i want to skip checking, but that's where i get lost. any help?

here's the code i'm working on.

Dim stdResponse As Variant
Dim ctl As Control

    ' Enumerate Controls collection.
    For Each ctl In Me.Controls
        ' Check to see if control is text box.
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
          ' Set control properties.
            With ctl
                If ctl.Value = "" Or IsNull(ctl.Value) Then GoTo 20
            End With
        End If
    Next ctl
DoCmd.Close
Exit Sub

20:

stdResponse = MsgBox("You have not completed the " & ctl.Name & "." _
& vbCr & "This is a required field, do you want to complete it now?" _
& vbCr & vbCr & "Select YES to return to the form." & vbCr & vbCr _
& "Select NO to exit the form SAVING the record.", vbYesNo, "Missing Data")

If stdResponse = vbYes Then
    ctl.SetFocus
    Exit Sub
Else 'vbNo
     Me.Undo
    DoCmd.Close
    DoCmd.OpenForm "frmMasterPortal"
End If

 

[Roku]

In Form design mode, there is a Tag property which you can set on both form and it's controls This can be any value you want.

In your ctl loop, add a check for the value of ctl.Tag (which will be null if not set).

Incidentally, the With statement in your loop has no effect, so you can remove it. I also suggest that you replace the GoTo 20 statement with Exit For. When the loop finishes, ctl will be set to Nothing if the Exit was not taken.

 

[redalert]

Have a look at the attached and see how I have used the Tag property to facilitate
data entry.

It is WIP and I have a few additions to make, one being that the record cannot be saved if mandatory text boxes are left blank.

I have tried to make the code generic and will adapt it so that it can be put into a class module and used for different forms instead of being replicated in all data entry forms.

 

[cbkebasen]

@ Roku and Redalert,

Thanks for the input, I'll try these tomorrow at work and get back at you guys about its development. Incidentally, does the tag-skip process also work on multi-valued list or simple check boxes? This thought just got to me just now, i'll also try these tomorrow. thanks guys!

 

[cbkebasen]

I got the codes working, as long as the objects are textbox and combobox. I tried including a multivalued combobox however i'm getting errors that i don't get if i use single value combo box. any ideas how to include multi value combo box or multi value list box?

here's the code:

If Not Me.Dirty Then
        DoCmd.Close
        DoCmd.OpenForm "frmMasterPortal"
    Else

Dim stdResponse As Variant
Dim ctl As Control
    ' Enumerate Controls collection.
    For Each ctl In Me.Controls
        
    'Check if Control is Required
    If ctl.Tag = "Required" Then
        
        ' Check to see if control is text box.
        
        If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Then
          ' Set control properties.
            With ctl
                If ctl.Value = "" Or IsNull(ctl.Value) Then GoTo 20
            End With
        End If
        End If
        
        
        
    Next ctl
    DoCmd.Close
    DoCmd.OpenForm "frmMasterPortal"
Exit Sub

20:
stdResponse = MsgBox("You have not completed the " & ctl.Name & "." _
& vbCr & "This is a required field, do you want to complete it now?" _
& vbCr & vbCr & "Click YES to complete the form." _
& vbCr & "Click NO to exit WITHOUT SAVING.", vbYesNo, "Missing Data")
If stdResponse = vbYes Then
    ctl.SetFocus
    Exit Sub
Else 'vbNo
    Me.Undo
    DoCmd.Close
    DoCmd.OpenForm "frmMasterPortal"
End If
End If

 

[Roku]

What do you want to check for in the Multi-value boxes? Do you want to check that an item is selected and that it's valid; that the box has some content; the number of items selected ... there are many possibilities. Let us know which conditions are 'valid' and an answer will be provided.

 

[cbkebasen]

Hello Roku, thanks for the quick reply. Basically I also want to perform the same test that I do with the other combo boxes- which is to make sure that required combo boxes (multi valued included) are filled out before update. The same goes to the multi value list boxes. cheers.

 

[Roku]

For a ListBox, you can check it ItemsSelected.Count value. This will be 0 if no items are selected.

I haven't come across multi-value ComboBoxes? I checked the properties and didn't find MultiSelect - what is it you refer to here?

Your code would be something like this:

Dim ctl As Control
For Each ctl In Me.Controls
  Select Case ctl.ControlType
    Case acComboBox
      If IsNull(ctl) Then Stop
    Case acListBox
      If ctl.ItemsSelected.Count = 0 Then Stop
    Case Else
  End Select
Next

I have used a Case statement, but your If list would have the same effect. I used Stop for simplicity to trap the empty set.

You might find it helpful to examine the properties of ctl in the Stop condition. If you right-click on ctl (the definition in code window) then select Add Watch, you will see the values at that point. You can determine what properties are available to test and what value(s) they hold.

 

[cbkebasen]

@Roku, the codes worked beautifully. much thanks!