Clear all controls in a form. (1 Viewer)

Han_S_84 · May 6, 2010

I have a form with a mixtue of controls on, Combo Boxes looking up different tables, text boxes controlled by what is selected in the combo box and plain text boxes. All controls are unbound.
I used a button to run code which inserts the values entered/selected into a table. I would like the button to then clear the form ready for the user to enter new values.
I can do this by closing and reopening the form - but this isn't very neat.

I have tried using me. with refresh or undo neither of which work, but give no error messages.

I have also tried the code

Dim ctl As Control
For Each ctl In Me.Controls
ctl = vbNull String
Next

This gives me a runtime error 438.
Please help!!

rainman89 · May 6, 2010

See if this helps you

http://www.access-programmers.co.uk/forums/showthread.php?t=191431&highlight=clear+controls+form

boblarson · May 6, 2010

Here's a function you can put in a standard module and then call it whenever you want any form cleared.

Code:

Function ClearAll(frm As Form)
Dim ctl As Control

For Each ctl In frm.Controls
   Select Case ctl.ControlType
      Case acTextBox
           ctl.Value = ""
      Case acOptionGroup, acComboBox, acListBox
          ctl.Value = Null
      Case acCheckbox
         ctl.Value = False
   End Select
Next
End Function

And then you call it by using

ClearAll Me

to clear the current form.

Han_S_84 · Jul 19, 2010

Hi thank-you for your reply. I find this works until I get to a text box which is dependant upon the value in another text box, therefore it is locked. Is it possible to skip just this text box.
I have tried using If statements but get myself in a muddle with the For and Next statements lining up.

vbaInet · Jul 19, 2010

Code:

      Case acTextBox
       If ctl.Name <> "[COLOR=Red][B]TextboxName[/B][/COLOR]" then
               ctl.Value = ""
           end if

Han_S_84 · Jul 19, 2010

Thank-you it works.

vbaInet · Jul 19, 2010

Goodie ! ! !

rabuayya · Feb 12, 2018

I don't know if I can post on this thread considering it is ~8 yrs old. I just stumbled on it and it really helped me clear all controls in my form except for a listbox. I am trying to deselect the items in the listbox.

My form contains text boxes and one list box. I created a btnClearAll button to run the above function when clicked.

All the text boxes do get cleared but the items in the listbox remain selected. Why would the items in the list box not get deselected?

Can someone help me with that?
Thanks

missinglinq · Feb 12, 2018

Is the Multi-Select Property of the Listbox set to anything other than None? If so, it no longer has a Value Property...hence

ctl.Value = Null

won't work. You'd have to have a separate clause for Listboxes and iterate thru them, unselecting each item...with something along the order of

Code:

Case acListBox For intLB = 0 to ctl.Name.ListCount - 1 ctl.Name.Selected(intLB) = False Next intLB

At the top of your Sub you should Dim the Variable intLB with

Dim intLB As Integer

Not my original code, but sadly, I don't have the source.

Linq ;0)>

rabuayya · Feb 12, 2018

Amazing. It works. Thank you.

N.C.Barrett · Sep 21, 2019

I tried that code in Access 2010, and

Code:

ClearAll Me

doesn't even get past the compilation stage. VBA throws "Compile Error: Expected variable or procedure, not module". I'm calling this from a private subroutine bound to a button, with

Code:

Option Compare Database
Option Explicit

at the very top.

Since I'm still learning how to build databases in Access, I'm not sure how to solve that. Thanks in advance for help.

Gasman · Sep 21, 2019

Sounds like you called the module name ClearAll.?

Name the module something else.
The actual function name should be as Bob posted.

HTH

N.C.Barrett · Sep 21, 2019

I didn't realize that was an issue. I'll try it tomorrow. Thanks.

N.C.Barrett · Sep 22, 2019

I tried that, and it works.

Clear all controls in a form. (1 Viewer)

Han_S_84

Registered User.

rainman89

I cant find the any key..

boblarson

Smeghead

Han_S_84

Registered User.

vbaInet

AWF VIP

Han_S_84

Registered User.

vbaInet

AWF VIP

rabuayya

Registered User.

missinglinq

AWF VIP

rabuayya

Registered User.

N.C.Barrett

New member

Gasman

Enthusiastic Amateur

N.C.Barrett

New member

N.C.Barrett

New member

Similar threads

Users who are viewing this thread