Clearing All Input Boxes

  • Thread starter Thread starter Michael Malone
  • Start date Start date
M

Michael Malone

Is there a quick way set all input controls to a null state.

Something like:

Dim frm As Form
Dim ctl As Control

Set frm = Screen.ActiveForm

For Each ctl In frm
ctl = vbNullString
Next

Am I on the right track?

Mike
 
Michael said:
Is there a quick way set all input controls to a null state.

Something like:

Dim frm As Form
Dim ctl As Control

Set frm = Screen.ActiveForm

For Each ctl In frm
ctl = vbNullString
Next

Am I on the right track?

Mike
Click to expand...

Bound form? This would rarely make sense then. Any required fields would raise
an error if you tried to clear them (Access won't wait until you save to
complain).

With an unbound form it might make more sense but not every control has a value
property so those will all raise errors (lines, labels, etc.). Of course with
an unbound form it is just as easy to simply close it and reopen it with two
lines of code if the controls are null by default.

To do it with a loop you will need to use the Tag property to identify the
controls to clear or else test the type of each control to make sure that
setting it to Null is appropriate. And you can just use Null rather than
vbNullString.
 
Rick,
Couldn't you just add "On Error Resume Next" at the beginning of the code to
skip over controls where Null is not appropriate?
Mark
 
Hi Micheal,

Depends on what you want to do. Word of caution, use with care! if you need
to bound the control source again, will need to bound the controls again one
by one in another code.

Dim frm As Form
Dim ctl As Control
Set frm = Me.Form
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acComboBox, acTextBox, acComboBox, acCheckBox, acListBox
'acCommandButton ',acLabel,
ctl.ControlSource = vbNullString
End Select
End With
Next
 
Mark said:
Rick,
Couldn't you just add "On Error Resume Next" at the beginning of the
code to skip over controls where Null is not appropriate?
Mark
Click to expand...

You could use that to skip controls that raise an error because they have no
Value property (lines, labels, etc.), but if you used it to skip controls bound
to required fields then you would also have to UNDO the change and the control
would stay at its old value. That doesn't seem like a satisfactory end result
to me.

You really haven't provided enough details for me to have a firm answer. As I
indicated previously clearing all controls on a form is not something that will
come up very often in most apps and really only makes sense in an unbound form.
Is this an unbound form?
 
Yes it is a bound form, but it does not have to be. What the form does get
user input, and goes out to the net to lookup GPS lat lon locations. The
information returned is then written down a piece of paper. So there is
only need for one record in the table, because of this I named the table
tmpLookupTbl.

I have a button on the form labeled "Clear Form". This should set the 12 or
so input boxes back to null and I am able to do it with statements like
this.

me.txtStreetAddress = vbNullString
me.cmbState = vbNullString
etc..

I was just wondering if there was a way that I could avoid all the
statements and use a more elegant solution.

Mike
 
Thanks this code put me on the right track. I just had to change the
ctl.ControlSource property to ctl.Value and it worked. This also removes
the bound control issues.

Thanks.

Mike
 
Back
Top