CODEWITHSUNDEEP
ms-accessif-statementtextboxvba
user1643333
user1643333

Reputation: 127

Loop through textboxes on access form and change the data

I am trying to change data when an access form opens. At the minute data says True and I would like to change that to say Yes instead. I have given it a go but I am new to vba and don't really know what I am doing. I have given all the textboxes the same Tag of 'QtTxtBox' hoping this would help but it hasn't. Below is what I've got so far, can anyone help me?

      Dim ctlVar As Control
      For Each ctlVar In Me.Controls
        If ctlVar.ControlType = acTextBox Then
            If acTextBox.text = "True" Then
               acTextBox.text = "yes"
            End If
      End If

Upvotes: 3

Views: 18994

Answers (3)

Tom Collins
Tom Collins

Reputation: 4069

Your problem is in your textbox reference. acTextBox is not a textbox. It's just a value showing that a control type is a textbox.
So, when you say 'If ctlVar.ControlType = acTextBox Then', that is correct.
But when you say 'If acTextBox.text = "True" Then', that is incorrect. You're not referencing the control any longer. It should be 'If ctlVar.text = "True" Then'. ctlVar is your reference to the control.

Also, you need to set the focus onto the textbox before changing the value. Here's what your code should look like:

For Each ctlVar In Me.Controls
  If ctlVar.ControlType = acTextBox Then
      If ctlVar.Value = "True" Then
         ctlVar.Value = "yes"
      End If
End If

Upvotes: 7

Gary Chernipeski
Gary Chernipeski

Reputation: 11

there are a few things packed into this little chunk of code -- perhaps even your issue -- after two days of figuring this stuff out...

Function disorder()
  For c = 0 To 9
    s = Screen.ActiveForm.Controls("" & "[" & c & "]" & "").Caption
    r = Int(9 * Rnd)
    Screen.ActiveForm.Controls("" & "[" & c & "]" & "").Caption = Screen.ActiveForm.Controls("" & "[" & r & "]" & "").Caption
    Screen.ActiveForm.Controls("" & "[" & r & "]" & "").Caption = s
  Next
End Function

To make sure focus is on the form I want I used SendKeys "{ENTER}" which is picked up by a default button on the form. In this case I have several controls named [1] and [2] etc to make it easier to loop through them but they could be named anything.

to loop through all the controls on a form...

For Each c In Screen.ActiveForm.Controls
  With c
    If Caption = "" Then Caption = "New"
  End With
Next

Upvotes: 0

Katana24
Katana24

Reputation: 8959

So - when you constructed the form you set the textbox caption to say True - correct? Well if you want to change that when the form loads then do the following:

Private Sub Form_Load()

      acTextBox.Caption = "Yes"

End Sub

Upvotes: -3

Related Questions