Error in For loop code (1 Viewer)

Tupacmoche · Jan 10, 2019

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

Code:

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 · Jan 10, 2019

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

Tupacmoche · Jan 10, 2019

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 · Jan 10, 2019

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

pbaldy · Jan 10, 2019

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

Tupacmoche · Jan 10, 2019

I still get the same error.

Code:

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 · Jan 10, 2019

How about?

Code:

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 · Jan 10, 2019

Tupacmoche said:
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 · Jan 10, 2019

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 · Jan 10, 2019

I believe you are still missing an End If.?

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

Tupacmoche · Jan 10, 2019

Gasman et. al

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

Dreamweaver · Jan 10, 2019

Cancel =True Can cause an error #2501

jdraw · Jan 10, 2019

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

Code:

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 · Jan 10, 2019

Yes, I noticed that typo and fixed it.

jdraw · Jan 10, 2019

So, is it working now?

Tupacmoche · Jan 10, 2019

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:

Code:

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 · Jan 10, 2019

No. I was looking at syntax.

Error in For loop code (1 Viewer)

Tupacmoche

Registered User.

Gasman

Enthusiastic Amateur

Tupacmoche

Registered User.

Attachments

pbaldy

Wino Moderator

pbaldy

Wino Moderator

Tupacmoche

Registered User.

Gasman

Enthusiastic Amateur

Gasman

Enthusiastic Amateur

Tupacmoche

Registered User.

Gasman

Enthusiastic Amateur

Tupacmoche

Registered User.

Dreamweaver

Well-known member

jdraw

Super Moderator

Tupacmoche

Registered User.

jdraw

Super Moderator

Tupacmoche

Registered User.

jdraw

Super Moderator

Similar threads

Users who are viewing this thread