Clear All Text Boxes on Form (1 Viewer)

[TheSearcher]

Hi All,

I use the following code all the time to render my text boxes invisible:

Dim cControl As Control
For Each cControl In Me.Controls
        If cControl.Name Like "text*" Then cControl.Visible = False
Next

However, now I want to clear all text boxes on my form. If I use cControl.value = "" I get an error.
Any ideas?

Thanks,
TS

 

[theDBguy]

Have you tried?

cControl.Value = Null

 

[TheSearcher]

I found a way with text boxes and frames but the combo box won't clear:

For Each cControl In Me.Controls
        If TypeName(cControl) = "TextBox" Then cControl.Value = ""
        If TypeName(cControl) = "Frame" Then cControl.Value = ""
        If TypeName(cControl) = "Combo Box" Then cControl.Value = ""    'This does not clear the box
Next

Null does not work either.

 

[MajP]

Private Sub ClearAll()

  Dim ctrl As Access.Control
  For Each ctrl In Me.Controls
   If ctrl.ControlType = acTextBox Or ctrl.ControlType = acComboBox Or ctrl.ControlType = acOptionGroup Then
     ctrl.Value = Null
   End If
  Next ctrl
End Sub

 

[theDBguy]

I found a way with text boxes and frames but the combo box won't clear:

For Each cControl In Me.Controls
        If TypeName(cControl) = "TextBox" Then cControl.Value = ""
        If TypeName(cControl) = "Frame" Then cControl.Value = ""
        If TypeName(cControl) = "Combo Box" Then cControl.Value = ""    'This does not clear the box
Next

Null does not work either.

Hi. Not sure why Null didn't work; but regarding clearing a Combobox, you can only assign a Null to it if it is unbound. Otherwise, you can try assigning False to it.

 

[TheSearcher]

MajP - I get the "You can't assign a value to this object" error.

 

[MajP]

If your object is a calculated control you cannot assign a value to it. Likely cause.

 

[MajP]

The reason it does not work with a Combo is that the typeName of a combobox is "Combobox" not "Combo Box". You are unlikely to ever find a space in a name.
And this is why I modified the code. I can look up the enumeration for access control types

AcControlType enumeration (Access)

Office VBA reference topic

docs.microsoft.com

You cannot lookup the typeName or at least I do not know where I can.

 

[MajP]

Also the typeName of an OptionGroup is "OptionGroup" not "Frame" so I doubt that works.

 

[LarryE]

You can try inserting this code as a Public Sub in your form then call it:
On Error GoTo ClearAll_Error
Dim ActiveForm As Form
Dim CntrlName As String
Set ActiveForm = Screen.ActiveForm
With ActiveForm
Dim ActiveCntrl As Control
For Each ActiveCntrl In ActiveForm.Controls
If ActiveCntrl.ControlType = acComboBox Or ActiveCntrl.ControlType = acOptionGroup Or ActiveCntrl.ControlType = acTextBox Then
ActiveCntrl = Null
End If
Next
End With
Exit Sub
ClearAll_Error:
DoCmd.CancelEvent
'MsgBox Err.DESCRIPTION
Resume Next
Exit Sub

 

[MajP]

Normally if you want a common procedure that clears controls, you tag the control with some common tag. In this example I select all the controls and in the tag property put the word "Clear" without any " around it in the Tag.

Public Sub ClearControls(Frm As Access.Form)
  On Error GoTo errlbl
  Dim cntrl As Access.Control
  For Each cntrl In Frm.Controls
    If cntrl.Tag = "Clear" Then cntrl.Value = Null
  Next cntrl
  Exit Sub
errlbl:
  MsgBox Err.Number & " " & Err.Description & " in Clear Controls"
  Resume Next
End Sub

Then from any form you can clear out the controls by tagging them and then calling the procedure
ClearControls Me

@LarryE, if you use the </> formatting icon you can format your code to make it readable. Fyi the With, end with does nothing in your case.

 

[Uncle Gizmo]

I have some code that you might find interesting!

What you do is you place a "frame/rectangle" around a group of controls and then you can can do something to the controls in the group.

See here:-

Nifty Container - Nifty Access

Nifty Container The "Nifty Container" offers you a graphical way of managing the controls on your form. All you do is surround a group of controls with a box, choose one of the coding examples, or write your own, and there you go, you have a simple and easy-to-use method, which avoids using your...

www.niftyaccess.com

If you would like a free copy of the example, then please contact me with a pm (private message) and I will explain how you can get a free copy...

 

[MajP]

If you would like a free copy of the example, then please contact me with a pm (private message) and I will explain how you can get a free copy...

Or you can simply use the procedure I posted for free and you do not even have to PM me. It is a pretty common method of tagging the controls. Not sure about why you would put a frame around the controls, but I guess you could if they were all in a common location.

 

[Uncle Gizmo]

Not sure about why you would put a frame around the controls,

One of the main benefits is that it is "visual" you just draw a box around the set of controls you want to perform in a particular way.

Imagine a scenario like a venn diagram, where you have one set of controls you want to perform in a particular way and another set of controls do you want to perform in a different way, and you have an overlap, where you have a few controls which you want to be exposed to both commands. I say both, it could be 3, 4, 5 or more rectangles/boxes.

 

[Uncle Gizmo]

you do not even have to PM me

I like to know if there is any interest in a particular method or piece of code I write.

I also like something in return for my free code. If someone sends me a PM, then I request that they join my YouTube channel, like a payment for the free code.

 

[Uncle Gizmo]

Not sure about why you would put a frame around the controls

I used the same code to create another new & novel product, I call it a 7 star system, where someone can click and give you a 7 Star rating.

The 7 Star rating system was developed by accident when I was creating the the Nifty Option Group, where you can use images for the Option Buttons, instead of the horrible looking things provided by Microsoft!

More info Here:-

Nifty Option Group – With 7 Stars - Nifty Access

Nifty Option Group – With 7 Stars The "Nifty Option Group" - With Images! The "Nifty Option Group with Seven Stars" evolved out of the Nifty Option Group based on Wingding Characters. More details on my website here:- Nifty Option Group - Wingdings. The Wingding Character Option Group is still...

www.niftyaccess.com

Again, if you'd like a free copy, pm me ...

 

[TheSearcher]

MajP - Your code works great and is very elegant. However, I added one line to it to deselect toggle buttons within an option group and now I'm getting the "2448 You can't assign a value to this object. In Clear Controls" error.

Public Sub ClearControls(Frm As Access.Form)

On Error GoTo errlbl

Dim cntrl As Access.Control
For Each cntrl In Frm.Controls
    If cntrl.Tag = "Clear" Then cntrl.Value = Null
    If cntrl.Tag = "oClear" Then cntrl = 0    'Trying to deselect toggle buttons within option group. This doesn't work
Next cntrl
Exit Sub

errlbl:
MsgBox Err.Number & " " & Err.Description & " in Clear Controls"
Resume Next

End Sub

Any ideas?

 

[MajP]

With an option group you want to set the value of the option group not the individual options. So only tag the option group frame.

 

[MajP]

This is interesting. The properties of an option are different inside an option group versus outside. Click on an option inside the option group and look at its data properties. Then copy that option and paste it outside the option group. Look at its properties. They are completely different. An option inside an option group does not even have a value property or a control source. It assigns a value to the option frame. This is why it failed for you.

 

[Pat Hartman]

If I use cControl.value = "" I get an error.
Any ideas?

"" doesn't clear anything. It places a ZLS (Zero Length STRING) in the control. As you should infer by its name, a ZLS is a STRING and therefore, you will get an error if you try to set a control to that value if the control is bound to a date or a numeric data type.

Null is the proper way to clear controls as theDBguy suggested a bit too gently.