CODEWITHSUNDEEP
ms-accessvbams-access-2010
BobbyLaMouelle
BobbyLaMouelle

Reputation: 25

loop through text boxes until empty string is found

Here is my code:

With me
If Len(.txtFactContact1) Or Len(.txtFactFonction1) Or Len(.txtFactTel1) 
Or  Len(.txtFactPosteTel1) Or Len(.txtFactCell1) 
Or Len(.txtFactCourriel1) Or Len(.txtFactNote1) Or Len(.txtFactContact2)
Or Len(.txtFactFonction2) Or Len(.txtFactTel2) Or Len(.txtFactPosteTel2)
Or Len(.txtFactCell2) Or Len(.txtFactCourriel2) Or Len(.txtFactNote2) <>
0 Then 
If MsgBox("Les Données Saisi seront perdus, Désirez-vous continuer?", vbExclamation Or vbYesNo, "Continuer?") = vbYes Then
        .Undo
     Else
        MsgBox "no"
     End If
 End If
End With

It's on a Cancel button so when there has been data entered in those textboxes it advertise that you're going to lose that data.

How could I reduce that Len() code at the beginning?

Upvotes: 2

Views: 1206

Answers (3)

PractLogical
PractLogical

Reputation: 258

Since all textboxes are being checked then this should work for you. It differs from the other answers in that it is a seperate function and that it's checking for Null values since a 'blank' textbox will give you errors when Null. Also, my Access 2010 Control object doesn't include ControlType as a member, so I'm using raw properties which should work for any version.

The Function:

Function TextsHaveText() As Boolean
  Dim ctl As Control

  TextsHaveText = False 'default is already False, but this wont hurt
  For Each ctl In Me.Controls

    If ctl.Properties("ControlType").value = acTextBox Then
      If Not IsNull(ctl.value) Then 'you could get an error if you don't check for nulls
        TextsHaveText = True
        Exit Function 'no point in going on if any text has value
      End If
    End If

  Next

End Function

The Call:

If TextsHaveText = True Then
  If MsgBox("Les Données Saisi seront perdus, Désirez-vous continuer?", vbExclamation Or vbYesNo, "Continuer?") = vbYes Then
    Me.Undo
  Else
    MsgBox "no"
  End If
End If

Just incase you decide to add non related textboxes, you can utilize the textbox .tag property to only look at certain textboxes.

[1] Set the all desired TextBox.tag properties to something that you can check for e.g. Text1.tag = "CHKLEN" (Leave non associated textbox tags blank)

[2] Alter the TextHaveText function to include acheck for .tag:

Function TextsHaveText() As Boolean
  Dim ctl As Control

  TextsHaveText = False
  For Each ctl In Me.Controls
    If ctl.Properties("ControlType").value = acTextBox Then
      If ctl.Tag = "CHKLEN" Then 'check the tag of the textbox control for the indicator
        If Not IsNull(ctl.value) Then 'you could get an error if you don't check for nulls
          TextsHaveText = True
          Exit Function 'no point in going on if any text has value
        End If
      End If
    End If

  Next

End Function

Upvotes: 0

HansUp
HansUp

Reputation: 97101

In a comment you indicated you want to check all the form's text boxes. So you needn't be concerned with the text box names. You can simply loop through all of them to see if any has a Value with Len > 0. 

Private Sub cmdCancel_Click()
    Dim ctl As Control
    With Me
        For Each ctl In .Controls
            If ctl.ControlType = acTextBox Then
                If Len(ctl.Value) > 0 Then
                    If MsgBox("Les Données Saisi seront perdus, Désirez-vous continuer?", _
                            vbExclamation + vbYesNo, "Continuer?") = vbYes Then
                        .Undo
                    Else
                        MsgBox "no"
                    End If ' MsgBox
                    Exit For
                End If ' Value
            End If ' ControlType
        Next
    End With
End Sub

Upvotes: 1

Tim
Tim

Reputation: 2892

Assuming you are checking the length of every textbox control on your form you could iterate through them and check the length like this.

Dim mytxt As TextBox
Dim c As Control
For Each c In UserForm1.Controls
    If TypeOf c Is msforms.TextBox Then
        If Len(c) > 0 Then Stop
    End If
Next

Upvotes: 0

Related Questions