VBA - Refer to all controls on a form

[Mi]

hello,
I am trying to find a code. I would like to check all the controls on a form, if null, then msg box to alert. Looking for something like:

if isnull(me.formALLCONTROLS) then
msgBox "no empty fields allowed"
end if
New record is then allowed

BTW, setting the fields on the table level to required has not worked, i have many subforms and it still allows a new record to be added.

Thanks,
Mi

 

[Norie]

Mi

Each form has a collection called Controls.

You could loop through this to check the value of each control.

By the way if you change the settings for a field in a table they will not be reflected in a form that was based on that table before the changes.

 

[Mi]

Norie,
It is the loop that I have problem with, could you provide a example of the code? I am still new in the vb aspect.

thanks,
Mi

 

[Norie]

You can cycle through all the controls on a form like this.

Dim ctl
     
     For Each ctl In Me.Controls ' Me is a reference to the form
          ' do something with the control ctl
     Next

You could test within the loop for the type or name of the control.

 

[Mi]

Thanks,
Works but... how to code if there are many, meaning only want one msg box to open regardless of how many are null. Currently, if there are 3, then the msg box opens 3 times.


Dim ctl

For Each ctl In Me.Controls
If IsNull(ctl) Then
MsgBox "empty fields"
End If
Next


Mi

 

[Norie]

Mi

Create a boolean variable say boolNull and try something like this.

Dim ctl 
Dim boolNull As Boolean

     For Each ctl In Me.Controls 
          boolNull = boolNull oR IsNull(ctl)
     Next ctl

     If boolNull Then
          MsgBox "empty fields" 
     End If

 

[Mi]

Last problem,
sorry to be a bother...where do I add the docmd for the new record if there are no blank fields. so far it still adds a new record.

Dim ctl
Dim boolNull As Boolean

For Each ctl In Me.Controls
boolNull = boolNull Or IsNull(ctl)
Next ctl

If boolNull Then
MsgBox "empty fields"
End If

DoCmd.GoToRecord , , acNewRec


End Sub

 

[Norie]

Try this

If boolNull Then 
     MsgBox "empty fields" 
Else
     DoCmd.GoToRecord , , acNewRec 
End If

 

[Mi]

Got it working!!!!!
Your the greatest.

Here is the final code. Reason for this is that new fields are being added and thus the form problem. This way, the new field can be added to table and form and the code takes care of the required elements. If the field is not a required element, (such as comments) added a default value. I did have to set the property to the form and subform to cycle thru only current record to prevent pressing the tab to create a new record and also remove the navigation buttons on the bottom but now updating has been keep to minimal time.

Again, thanks a big bunch!
Mi

Dim ctl
Dim boolNull As Boolean

For Each ctl In Me.Controls
boolNull = boolNull Or IsNull(ctl)
Next ctl

If boolNull Then
MsgBox "Please complete the required fields"
End If

For Each ctl In Me.frmSubform.Controls
boolNull = boolNull Or IsNull(ctl)
Next ctl

If boolNull Then
MsgBox "A review type has not been selected or scored"

Else
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToControl "office"
End If
End Sub

 

[Mi]

One more item, how would I go about reversing this? Meaning if all fields are empty, then ok to close form?

thanks,
Mi