Required fields based on a condition

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Trauton - 15 Jun 2007 23:55 GMT

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

Reply to this Message

Bob Quintal - 15 Jun 2007 23:47 GMT

> Hi,
>
[quoted text clipped - 29 lines]
> 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..

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

Trauton - 16 Jun 2007 01:50 GMT

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

> > Hi,
> >
[quoted text clipped - 55 lines]
>
> Learn to indent your code. It makes it a lot easier to debug..

Reply to this Message

Rob Parker - 16 Jun 2007 02:47 GMT

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

> Thank you Bob. I adapted the code as follows and placed it in the
> BeforeUpdate event of the form:
[quoted text clipped - 86 lines]
>>
>> Learn to indent your code. It makes it a lot easier to debug..

Reply to this Message

Trauton - 16 Jun 2007 05:09 GMT

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

> The code in your outer If statement will never execute, because the
> condition syntax is wrong. Try:
[quoted text clipped - 95 lines]
> >>
> >> Learn to indent your code. It makes it a lot easier to debug..

Reply to this Message

PiB311 - 20 Jun 2007 22:18 GMT

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

Reply to this Message

Rob Parker - 21 Jun 2007 01:52 GMT

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
[quoted text clipped - 130 lines]
>> >>
>> >> Learn to indent your code. It makes it a lot easier to debug..

Reply to this Message