Required fields based on a condition

[Bob Quintal]

=?Utf-8?B?VHJhdXRvbg==?= <[email protected]>
wrote in

Hi,

I have the following controls in a form: DateCompleted,
Margin1 and Margin2.
I need to set up the form so that anytime a date is populated
in the
DateCompleted control, the form will require that the controls
Margin1 and Margin2 are not null.

I found the following language in the forum that I have tried
to incorporate in the BeforeUpdate event in the form but I am
having a hard time making it work. Could someone show me how
should this language look based on my controls? I think I am
the If statement text.

Dim ctl As Object

If Me.YourCheck = True Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox Or ctl.ControlType =
acComboBox) And ctl.Tag = "Validate" Then
If Nz(ctl, "") = "" Then
MsgBox("You have to fill out all the controls first!")
Cancel = True
End If
Exit For
End If
End If
Next ctl

Thank you!!
Trauton

You seem to have the order of some End If and Next CTL reversed.

If Me.YourCheck = True Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
MsgBox("You have to fill out" _
& " all the controls First!")
Cancel = True
Exit For
End If
End If
Next ctl
End If

Should work. Notice the indentation. See how the For each and
Next now fall at the same level of indent. I also moved the Exit
For to inside the If Nz, because that's where it wants to be.
That became understandable only when indented.

Learn to indent your code. It makes it a lot easier to debug..

 

[Guest]

Hi,

I have the following controls in a form: DateCompleted, Margin1 and Margin2.
I need to set up the form so that anytime a date is populated in the
DateCompleted control, the form will require that the controls Margin1 and
Margin2 are not null.

I found the following language in the forum that I have tried to incorporate
in the BeforeUpdate event in the form but I am having a hard time making it
work. Could someone show me how should this language look based on my
controls? I think I am the If statement text.

Dim ctl As Object

If Me.YourCheck = True Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) And ctl.Tag
= "Validate" Then
If Nz(ctl, "") = "" Then
MsgBox("You have to fill out all the controls first!")
Cancel = True
End If
Exit For
End If
End If
Next ctl

Thank you!!
Trauton

 

[Guest]

Thank you Bob. I adapted the code as follows and placed it in the
BeforeUpdate event of the form:

If Me.DateCompleted = NotNull Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
MsgBox("You have to fill out" _
& " all the controls First!")
Cancel = True
Exit For
End If
End If
Next ctl
End If

I then went to the Margin1 and Margin2 fields and changed the Tag property
in the Other tab to: "Validate". Although the DateCompleted control has a
date in it, the form doesn't prompt me to enter informtion in the Margin1 and
Margin2 controls. What am I missing?

Sorry about the indentation, I think it is lost when you copy it to these
messages.

Trauton

 

[Rob Parker]

The code in your outer If statement will never execute, because the
condition syntax is wrong. Try:
If Not IsNull(Me.DateCompleted) Then
...

HTH,

Rob

 

[Guest]

Rob,

That did it! Thank you VERY much for your help! Just in case this helps
someone else, I also wanted to add a condtion so that the fields would not
become required if the control OppType was equal to "APX" This is the code
that did it:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Object
If Not IsNull(Me.DateCompleted) And Not (Me.OppType = "APX") Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
MsgBox ("You have to fill out" _
& " all the Controls First!")
Cancel = True
Exit For
End If
End If
Next ctl
End If
End Sub


Trauton

 

[Guest]

will the code posted work to verify two fields or just one? this is what I
have and it works until the first instance of null is found and then quits.
I need it to find all instances of null, highlight all fields, and then after
it cycles through all items pops up a message box stating fields are not
complete...



If Me.combo_addform = "None" Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
ctl.BackColor = 16777088
int_verify = 1
End If
End If
Exit For
Next ctl

If int_verify = 1 Then
MsgBox ("Please Complete the Highlighted Fields")
'Else
' DoCmd.RunSQL str_sql
End If
End If

 

[Rob Parker]

In its current form, the code will only test the first control. That's
because you are explicitly telling it to exit from the For loop. If you
remove the
Exit For
line all should be well, since your int_verify flag will be set when any
control is invalid, and all controls (of the types you have specified) will
be tested and highlighted if necessary. BTW, I assume that you have a
statement to set int_verify to 0 (or some other value) before you get to
this section of code. It may not strictly be necessary (depends on where
this code is called from, and if it's part of a larger loop), but it's
certainly good programming practice.

HTH,

Rob

will the code posted work to verify two fields or just one? this is what
I
have and it works until the first instance of null is found and then
quits.
I need it to find all instances of null, highlight all fields, and then
after
it cycles through all items pops up a message box stating fields are not
complete...



If Me.combo_addform = "None" Then
For Each ctl In Me.Controls

If (ctl.ControlType = acTextBox _
Or ctl.ControlType = acComboBox) _
And ctl.Tag = "Validate" Then

If Nz(ctl, "") = "" Then
ctl.BackColor = 16777088
int_verify = 1
End If
End If
Exit For
Next ctl

If int_verify = 1 Then
MsgBox ("Please Complete the Highlighted Fields")
'Else
' DoCmd.RunSQL str_sql
End If
End If