Clear All Text Boxes on Form

[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)

Access VBA reference

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.

 

[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.

 

[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.

 

[TheSearcher]

MajP - The funny thing is that as soon as I get the error the option group is cleared - so it would work just fine if it didn't produce an error. I can work around this by setting all the options to 0 individually but I was hoping I could do this in a more efficient manner.
Pat - Thanks for chipping in. I've been clearing controls using the zero length string for about twenty years. I never use bound controls so perhaps that's why it never caused a problem.

 

[MajP]

MajP - The funny thing is that as soon as I get the error the option group is cleared - so it would work just fine if it didn't produce an error. I can work around this by setting all the options to 0 individually but I was hoping I could do this in a more efficient manner

Like I said only tag the option group frame. I am not sure how much clearer you want me to make it. Do not mess with the individual options. What part of that do you not understand?

 

[MajP]

I've been clearing controls using the zero length string for about twenty years. I never use bound controls so perhaps that's why it never caused a problem.

If you think about this logically it makes no sense to make it ZLS and not null. There is almost no way to have an "empty" control be a ZLS unless you do it through code or an import from another source. An "empty" control defaults to Null and there is no way through the GUI to make it ZLS. So you are forcing a ZLS when it does not belong. If you open a blank record those fields are NULL. If you enter a value and then delete the value it is NULL and not ZLD. So IMO it is putting a square peg in a round hole.

 

[TheSearcher]

Pat: That is correct.
MajP: I am a stupid man. I am an idiot. What didn't you think I understood?

 

[MajP]

MajP: I am a stupid man. I am an idiot. What didn't you think I understood?

Sorry I was not suggesting anyone was in any way not smart. I just foot stomped in thread 18 that you need to change the value of the option group and not the individual options. So you must only tag the frame and not the options. In thread 19 I explained in detail why this is. An option button inside an option group does not have a value property so you cannot try to change it without receiving an error. You already saw what happens if your try.

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.

But after my explanation you stated

I can work around this by setting all the options to 0 individually but I was hoping I could do this in a more efficient manner.

But as I explained in 19 this is impossible.

So the thing I am guessing you did not understand was to Only Tag the frame of the option group and not any option.