For Each Object In Form...

[Eka1618]

Hello,

I have several forms (including subforms) that I want to loop through before
a person exits that form to check if all fields are not null.

I wanted to put this in a module and then call it again in each form where
needed.
I tried to call the function in the Lost Focus Event, but I'm not sure if
that would work when it comes to checking a subform and its parent form, so
then I tried the deavtivated event, and did not get anywhere...

I have text boxes, combo boxes and list boxes if that helps. I have posted
my code below. If anyone has any tips on what I can do, please let me know,
Thank you!

~Erica~

Here is the function I created in my module:

Public Function CheckIfNull(frm As Form) As Boolean
Dim ctrl As Control

CheckIfNull = False

For Each ctrl In frm.Controls
If ctrl Is Null Then
CheckIfNull = True
End If
Next

End Function

Here is a sample of code inside a form:

Private Sub Form_Deactivate()
Dim ctrlNull As Boolean
ctrlNull = CheckIfNull(Me)

If ctrlNull = True Then
MsgBox "You must fill out all fields in General Information before
moving on."
End If
End Sub

 

[Daniel Pineault]

You're off to a good start. Now take a look at the ControlType Property to
determine what type of control you have and then only perform your check for
null against Textbox, cbo and lst

Something along the line of...

Dim ctl As Control

For Each ctl in frm.Controls
With ctl
Select Case .ControlType
Case acComboBox

Case acTextBox

Case acListBox

End Select
End With
Next ctl
....

You can actually put all the cases together in one case statement since you
will be running the same test on them regardless of their specific type.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.com/index.php
Please rate this post using the vote buttons if it was helpful.

 

[Klatuu]

Here is a function that does it. It returns the name of the first control
that is either Null or = vbNullString
You just call it like:
strValid = CheckFormValues(Me)
If strValid <> vbNullString Then
"Control " & strValid & " Requires an Entry"
Me.Controls(strValid).SetFocus
End If

Public Function CheckFormValues(frmCheck As Form) As String
'Pass the name of the OPEN form to check
'Returns vbNullString if no errors found
'Returns the name of the control that fails
Dim ctl As Control
Dim lngCtlCount As Long
Dim lngX As Long

CheckFormValues = vbNullString
For Each ctl In frmCheck.Controls
With ctl
lngX = .ControlType
If Nz(Switch(lngX = acTextBox, True, _
lngX = acComboBox, True, _
lngX = acListBox, True, _
lngX = acOptionGroup, True, _
lngX = acCheckBox, True) _
, False) Then
If Nz(.Value, vbNullString) = vbNullString Then
CheckFormValues = .Name
Exit For
End If
End If
End With
Next ctl
End Function

 

[Eka1618]

Dave,

This is working nice. I am trying to maybe figure out a way to
highlight/change color the control that is null. Returning the name of the
control may cause a problem for my users only because of how I named my
controls (ie. cmbLookUp, txtTitle).

If you know of how I can highlight these controls please let me know. If
not, I could just tell them that it will place the cursor back to the field
that needs to be entered. Thank you!

~Erica~

 

[Eka1618]

Dave,

The other thing I wanted to ask is how can I call this function when moving
from one form to the next? What I mean is that I have the parent form and
several child forms, so when I move from one to the next, how do I call this
function? I have tried the deavtivated and lost focus events so far any no
luck.

If you know what I can do here please let me know, Thank you!

~Erica~

 

[Eka1618]

Nevermind about the color thing, I know how I want to do it now.

~Erica~

 

[Eka1618]

I have come up with the code below. It is sort of working... on the Exit
event of the subform object, is saying that come of the controls are Null
when the are set with default values. For instance, a couple of my coombo
boxes have the option "N/A", and this is set as the default value. If a user
leave it like this, the function returns that control as having a null value.
It seems to work a little... there are still some weird bugs thought to work
out

Here is the Exit event of one of my subforms:

Private Sub frmLock1_Exit(Cancel As Integer)
Dim strValid As String

strValid = CheckFormValues(Me.frmLock1.Form)

If strValid <> vbNullString Then
MsgBox "All fields must be filled before Continuing"
Me.frmLock1.Form.Controls(strValid).SetFocus
End If
End Sub

Here is the function that I redid a little:

Public Function CheckFormValues(frmCheck As Form) As String
'Pass the name of the OPEN form to check
'Returns vbNullString if no errors found
'Returns the name of the control that fails
Dim ctl As Control
Dim lngCtlCount As Long
Dim lngX As Long

CheckFormValues = vbNullString
For Each ctl In frmCheck.Controls
With ctl
lngX = .ControlType
If Nz(Switch(lngX = acTextBox, True, _
lngX = acComboBox, True, _
lngX = acListBox, True)) Then
If Not IsNull(Nz(.Value, vbNullString)) Then
frmCheck.Controls(ctl.Name).SetFocus
Screen.ActiveControl.BackColor = vbWhite
End If

If Nz(.Value, vbNullString) = vbNullString Then
CheckFormValues = .Name
frmCheck.Controls(ctl.Name).SetFocus
Screen.ActiveControl.BackColor = vbYellow
Exit For
End If
End If
End With
Next ctl
End Function

 

[Klatuu]

The code should be in the Before Update event of the form you want to check,
even if it is a subform. That way, you can cancel the update.

 

[Eka1618]

Dave,

The Before Update was definately an easier approach than what I did, but I
am still getting the same errors when determining if an object on the subform
is null or not. It is saying that certain fields are null when I clearly have
values in them... I'm not sure why it is doing that. I will say that it is
null even if I have a default value that the user does not change...

~Erica~

 

[Klatuu]

I don't know why that is happening. I would have to do some testing to see
what is happening. So, you are saying that you have controls with Default
Values assigned and when the code runs, it says the control is Null? Is the
default value showing on the form?

 

[Eka1618]

Dave,

Nevermind about the last question. I do not know what I did, but it seems to
be working now. I believe the only thing i changed was the early exit out of
the for loop.... I do not know why that made a difference, but now, it will
highlight all fields in the form at once instead of steping through them
all...

I am still going to set validation rules for each field I think. If the user
does not enter all values it will not enter the record, but at this point
they still could press the send btn if one of the subforms do not have all
fields filled out, they just do not get a message that the record is not
added for some reason.

I am going to keep working on this for now, Thank you for the help and if
you have any suggestions regarding the new issue, please let me know

~Erica~