Checking to see that all fields on a form have a value

[rincewind_wizzard]

Hi,

I need users to enter values in about 8 unbound fields, then they click a button to run a routine. I know I can check that there are values in the unbound fields by going down the if is null xx or if is null yy etc. etc. route but I know there is a way of cycling through all the unbound fields, checking that there is a values in them via some kind of VB loop. Can anyone post an example for me?

Thanks

Paul

 

[CJ_London]

you can't cycle through just unbound controls only all controls, either for the form or for a section perhaps something like

dim ctrl as control
for each ctrl in me.controls 'or me.detail.controls
    if ctrl.controltype<>acLabel then ' or = type of control, you may need to exclude others
        if ctrl.controlsource="" then 'control is unbound
           if nz(ctrl,"")="" then 'ctrl has not been completed
               msgbox "complete " & ctrl.name
           end if
        end if
    end if
next ctrl

 

[rincewind_wizzard]

Thanks CJ_London - that's exactly what I had in mind. I'm currently near Liverpool St Station btw - in London too.

 

[rincewind_wizzard]

Is your luggage following you?


Sitting right next to me. With Greebo the cat. Only eaten two people so far today - and that's just Greebo

 

[missinglinq]

As a twist on CJ's code, you can only loop certain designated Controls by using the Tag Property.

Select the appropriate Control(s) then got to Properties - Other and enter a value in the Tag Property. Don't get confused if you see a ***SmartTag*** Property! This is something else entirely!

For the purposes of this demo, we'll make the Tag Property

Required

Enter this in the Tag Property box (without quotation marks) of all Controls to be checked:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control
Dim CName As String

For Each ctl In Me.Controls
    Select Case ctl.ControlType
        Case acTextBox
            If ctl.Tag = "Required" And IsNull(ctl) Then
              CName = ctl.Controls(0).Caption
              MsgBox "Following field is required: " & vbCrLf & CName
              Cancel = True
              ctl.SetFocus
              Exit Sub
           End If
    End Select
Next ctl

End Sub

Now only the 'tagged' Controls will be checked.

BTW...if you have a large number of Controls to be 'tagged,' you can do this is one fell swoop:

In Form Design View

1. Hold down <Shift> and Left Click on each Control in turn.
2. Go to Properties - Other
3. Enter the desired Tag (without quotation marks)
4. Save the Form
   

You're done!

Linq ;0)>

 

[GinaWhipp]

Hmm, I use...
https://www.access-diva.com/vba13.html

...which use the Tag property.