Error in For loop code

[Tupacmoche]

VBA Masters,

I have this simple for loop that gives me an error but, I can't see what's wrong any help is appreciated. Error msg is: Next without for

For Each ctl In Me.Detail.Controls
    With ctl
        If .ControlsType = acTextBox Then
            If IsNull(.Value) Then
                If .Tag = "v" Then
                   If MsgBox(ctl.Name & " is empty, this is a required field", vbOKCancel) = vbCancel Then
                    Cancel = True: ctl.SetFocus: Exit Sub
                End If
            End If
        End If
    
Next ctl

 

[Gasman]

Try putting it between code tags with indentation and then it should be easy to see the problem.?

 

[Tupacmoche]

I do have it formatted but the formatting is not kept when you attach the code into this editor. Please see attached screen shot. It still eludes me.

 

[pbaldy]

I added code tags to the original post. Using the "#" icon in the reply area adds code tags which preserves indenting.

 

[pbaldy]

Oh, and you didn't end the With block.

 

[Tupacmoche]

I still get the same error.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In Me.Detail.Controls
    With ctl
        If .ControlsType = acTextBox Then
            If IsNull(.Value) Then
                If .Tag = "v" Then
                   If MsgBox(ctl.Name & " is empty, this is a required field", vbOKCancel) = vbCancel Then
                    Cancel = True: ctl.SetFocus: Exit Sub
                End If
            End If
        End If
Next ctl
    End With
    
End Sub

 

[Gasman]

How about?

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In Me.Detail.Controls
    With ctl
        If .ControlsType = acTextBox Then
            If IsNull(.Value) Then
                If .Tag = "v" Then
                   If MsgBox(ctl.Name & " is empty, this is a required field", vbOKCancel) = vbCancel Then
                    Cancel = True: ctl.SetFocus: Exit Sub
                End If
            End If
        End If
    End With
Next ctl
    
    
End Sub

 

[Gasman]

I do have it formatted but the formatting is not kept when you attach the code into this editor. Please see attached screen shot. It still eludes me.

That is why there are code tags?

Are you saying you have made over 200 posts and never used the code tags?

 

[Tupacmoche]

Gasman,

As long as I have been on this site, I have never even considered the formatting tools that are on top of the editing form. But, I will use it going forward. I dropped your suggested code in and got the error: End With without With.

 

[Gasman]

I believe you are still missing an End If.?

Pair them up, I think the one for MSGBOX is missing?

 

[Tupacmoche]

Gasman et. al


Thanks, I saw that but I'm so tired that, I did not close it.

 

[Dreamweaver]

Cancel =True Can cause an error #2501

 

[jdraw]

I have added an End If re the msgBox, but I think the property is ControlType not ControlsType.
What is the purpose of bCheck?

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In Me.Detail.Controls
    With ctl
        If .ControlsType = acTextBox Then
            If IsNull(.Value) Then
                If .Tag = "v" Then
                   If MsgBox(ctl.Name & " is empty, this is a required field", vbOKCancel) = vbCancel Then
                    Cancel = True: ctl.SetFocus: Exit Sub
                   [COLOR="Blue"]End if 'for the msgbox *********[/COLOR]
                End If
            End If
        End If
    End With
Next ctl
    
    
End Sub

 

[Tupacmoche]

Yes, I noticed that typo and fixed it.

 

[jdraw]

So, is it working now?

 

[Tupacmoche]

Jdraw,


It may be that you could see that the code was going to crap out as it did. I changed it to the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim bCheck As Boolean
Dim ctl As Control

For Each ctl In Me.Detail.Controls
    With ctl
            If .Tag = "v" Then
                If IsNull(.Value) Then
                    MsgBox ctl.Name & " is empty, this is a required field!", vbOKOnly, "Required Field"
                    ctl.SetFocus
                    Cancel = True
                    Exit Sub
                End If
            End If
    End With
Next ctl
    
End Sub

The code in the thread was showing the message but nothing else. Did you know that?

 

[jdraw]

No. I was looking at syntax.