Field Requirements based on conditional visibility

[dkmoreland]

I have a form on which certain fields are only visible when the correct value is chosen in the combo box.

Here is the code I have in the After Update property for the combo box - it is working fine.

Private Sub Issue_Type_AfterUpdate()
If Me.[Issue Type] = "Bindery" Then
Me.[Roll #].Visible = False
Me.Lot.Visible = False
Me.cboPressInitials.Visible = False
Me.[Box Range].Visible = True
Me.[Last Box Number].Visible = True
Else
Me.[Roll #].Visible = True
Me.Lot.Visible = True
Me.[Box Range].Visible = False
Me.[Last Box Number].Visible = False
Me.cboPressInitials.Visible = False
End If
End Sub

However, I need these fields to be required if they are visible. I am using this code on the Before Update property of the form to check to make sure the required fields are populated. I didn't write this - some kind soul posted it somewhere else and I borrowed it.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
Dim ctl As Control, DL As String

DL = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.Tag = "?" Then
If Trim(ctl & "") = "" Then
Msg = "Required field '" & ctl.Name & "' cannot be blank"
Style = vbCritical + vbOKOnly
Title = "Required Data Error! . . ."
MsgBox Msg, Style, Title

Me(ctl.Name).SetFocus 'set focus to the field in error.
Cancel = True 'cancel saving the record!
Exit Sub 'return to the form for correction.
End If
End If
Next

End Sub

If I set the tags to ? on those fields that have conditional visibility, I get a Set Focus error, since focus can't be set on an invisible field.

How can i set these fields to be required when visible? is there a smoother way to do this?

Thanks in advance!

 

[static]

If visibility is based on Issue Type=Bindery then the condition is the same in both cases. Otherwise, I don't understand. I don't see you setting the tag anywhere.

Private Sub Issue_Type_AfterUpdate()
	Me.cboPressInitials.Visible = False
	dim b as boolean
	b=Me.[Issue Type] = "Bindery"
	Me.[Roll #].Visible = not b
	Me.Lot.Visible = not b 
	Me.[Box Range].Visible = b
	Me.[Last Box Number].Visible = b
End Sub

Private Sub Issue_Type_BeforeUpdate..
	dim b as boolean
	b=Me.[Issue Type] = "Bindery"
	if b then
		if len("" & Me.[Box Range]) and len("" & Me.[Last Box Number]) then
		    'do whatever
                else
                    'required stuff not filled
		end if
	else
		if len("" & Me.[Roll #]) and ("" & me.Lot) then
		    'do whatever
                else
                    'required stuff not filled
		end if
	end if
End Sub

 

[dkmoreland]

I'm setting the tag to ? in the Control property for each field on the form.

 

[static]

But that's not based on any condition.
Have a think about what I wrote above for a bit.

 

[dkmoreland]

But that's not based on any condition.
Have a think about what I wrote above for a bit.

I certainly will. I just know I have overlooked something. But it has been a long day and I believe I'm finished thinking until tomorrow.

I'll look at it then. Thank you for your help.

 

[Solo712]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg As String, Style As Integer, Title As String
   Dim ctl As Control, DL As String
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If [COLOR="red"]ctl.Visible = "True"[/COLOR] And ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            Msg = "Required field '" & ctl.Name & "' cannot be blank"
            Style = vbCritical + vbOKOnly
            Title = "Required Data Error! . . ."
            MsgBox Msg, Style, Title
            
            Me(ctl.Name).SetFocus 'set focus to the field in error.
            Cancel = True         'cancel saving the record!
            Exit Sub              'return to the form for correction.
         End If
      End If
   Next

The quickest way to fix your problem is to add the code in red above in the BEFORE UPDATE event.

Best,
Jiri

 

[Galaxiom]

 If [COLOR=red]ctl.Visible = "True"[/COLOR] And ctl.Tag = "?" Then

The quickest way to fix your problem is to add the code in red above in the BEFORE UPDATE event.

True in this context is a constant, not a string. It should not have quotation marks around it.

 

[Solo712]

True in this context is a constant, not a string. It should not have quotation marks around it.

You are right, should be ctl.Visible = True

Best,
Jiri

 

[dkmoreland]

The quickest way to fix your problem is to add the code in red above in the BEFORE UPDATE event.

Best,
Jiri

That works great! I knew it was something simple that I was overlooking. Thanks very much.