Checking to see that all fields on a form have a value (1 Viewer)

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

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

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

rincewind_wizzard said:

I'm currently near Liverpool St Station btw - in London too.

Click to expand...

Is your luggage following you?


Sent from my SM-G925F using Tapatalk

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:

Code:Copy to clipboard

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)>

You have to be careful when you check for a Caption like this "CName = ctl.Controls(0).Caption". If a Control doesn't have a label then you will get an error "Run time Error 2467".

I wrote some code recently to make sure that a Control had a label to avoid this particular error. Actually, the code did the opposite, it checked to see if a label had a Control. VBA is funny like that, you can't say does this or that object exist? If you try and look at an object directly, an object that does not exist, then you automatically get an error. The code is in the CallCalled Class module that can be found HERE:-

You could handle "Run time Error 2467"... But, it's considered good practice to NOT use avoidable errors to control the logic of your code. But then that begs the question if the object doesn't exist, then how do you access the object to check if it exists or not? How do you ask the question:- "Does this object exist?"

The solution is to remember that all objects have a name, and all objects belong to a Collection. Instead of directly testing to see if an object exists, and by doing so causing an error, you pass the objects name into code which runs through the collection and says, do you have a member of this collection with this name? If yes then the object exists, if no then the object does not exist...

In other words you never touch the object, you just look for a missing object, an object missing from the collection.

It took me many hours, nay, many days, nay, many months to learn this lesson! Here endeth the 255th lesson...

Note to Self, an alternative method of finding if the control has a label or not might be to access the controls own control collection. It's hinted at in this line "Ctrl.Controls(0).Caption"... In theory the 0 is an index to the Control in the collection of the Controls of "Ctrl"... You might be able to ask a different question, you could ask does this control have a control 0? --- no idea if it will work!

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

...which use the Tag property.