Clear All Controls on Form

[ryguy7272]

I have been using the code below in an Excel UserForm for a while without any
problems.

Private Sub Command337_Click()

Dim C As MSForms.Control
For Each C In Me.Controls
If TypeOf C Is MSForms.TextBox Then
C.Text = ""
End If
Next C

End Sub

I copied/pasted it into an Access Form so I could clear the values of all
Controls, but I get a message that says Compile Error: User-defined type not
defined. Are the Controls in Access different from the controls in Excel? I
read several posts on this DG; still not able to clear all the Controls in my
Access Form.

I have several textboxes and several ComboBoxes. How can I clear all values
in all Controls in my Access Form? Set to Null?

Thanks,
Ryan---

 

[Jim Burke in Novi]

Try just using:

Dim C As Control

 

[Dirk Goldgar]

I have been using the code below in an Excel UserForm for a while without
any
problems.

Private Sub Command337_Click()

Dim C As MSForms.Control
For Each C In Me.Controls
If TypeOf C Is MSForms.TextBox Then
C.Text = ""
End If
Next C

End Sub

I copied/pasted it into an Access Form so I could clear the values of all
Controls, but I get a message that says Compile Error: User-defined type
not
defined. Are the Controls in Access different from the controls in Excel?
I
read several posts on this DG; still not able to clear all the Controls in
my
Access Form.

I have several textboxes and several ComboBoxes. How can I clear all
values
in all Controls in my Access Form? Set to Null?

Yes, the Access Control object is completely different from the MSForms
controls used in Excel and Word. Also, an Access control's Text property is
only available under special circumstances, and is only used for very
special purposes. It's the Value property -- which is the default property
of most Access controls, and hence need not be named explicitly -- that you
would normally work with. Further, you mostly want to set controls to Null
to clear them, not to "", because "" is not a valid value for many controls.

With all that in mind, we can revise your code as follows:

'----- start of revised code -----
Private Sub Command337_Click()

Dim C As Access.Control ' or just "As Control"

For Each C In Me.Controls
If TypeOf C Is Access.TextBox Then
C = Null
End If
Next C

End Sub
'----- end of revised code -----

 

[dymondjack]

I'm not *positive* this is it, but I *think* it should work

Dim frm as Form
Dim ctl as Control

Set frm = "formname"
For Each ctl in frm.controls
If ctl.ControlType = acTextBox Then ctl.Value = ""
Next

Set ctl = Nothing
Set frm = Nothing

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[Dirk Goldgar]

When I tried the first example, I got a message that said ‘Compile Error:
Method or data member not found’. This line was highlighted blue:
For Each C In Me.Control

When I tried the second example, I got a message that said ‘Compile Error:
Type Mismatch’. This line was highlighted blue:
Set frm = "ConsultForm"
ConsultForm is the name of my Form.

I modified the third example:
Dim C As Access.Control ' or just "As Control"

For Each C In Me.Controls
If TypeOf C Is Access.TextBox Then
C = Null
Else
If TypeOf C Is Access.ComboBox Then
C = ""
End If
End If
Next C

When I tried the modified code, I got this message: ‘run-time error 2448:
you can’t assign a value to this object’
This line is highlighted yellow:
C = Null

The TextBoxes and ComboBoxes were cleared, but I still get that error
message. I don’t think I should just add code to handle the error, should
I.
Do I have to Dim the TextBox and ComboBox separately? I must be missing
something simple here, but I just don’t know what.

I don't know what your "first example" and "second example" are; I only
posted one block of revised code. But it looks like you didn't fully
understand my post, because you added these lines:

If TypeOf C Is Access.ComboBox Then
C = ""
End If

.... even though I told you to use Null instead of "".

Your error may come from calculated or other non-updatable controls on the
form. I suggest you trap and ignore that error:

'----- start of revised code -----
Private Sub Command337_Click()

On Error Err_Handler

Dim C As Access.Control ' or just "As Control"

For Each C In Me.Controls
If TypeOf C Is Access.TextBox _
Or TypeOf C Is Access.ComboBox _
Then
C = Null
End If
Next C

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2448 Then
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

End Sub
'----- end of revised code -----

 

[ryguy7272]

You are correct Dirk! I have one bound textBox, and this seems to be causing
the error. The bound TextBox is named CurrentDate. I tried the code below;
still no success.

Private Sub Command337_Click()

Dim C As Access.Control ' or just "As Control"

For Each C In Me.Controls
If CurrentDate.Value <> "" Then
If TypeOf C Is Access.TextBox Then
C = Null
Else
If TypeOf C Is Access.ComboBox Then
C = Null
End If
End If
End If
Next C

On Error Resume Next

End Sub

 

[ryguy7272]

Dirk, the code from your second post works great! I retried the code from
jim and dymondjack, with the bound TextBox gone, but that code didn't work.
Out of curiosity, is there way to tell Access to disregard the TextBox named
CurrentDate?

Thanks for everything!!
Ryan--

 

[Dirk Goldgar]

Dirk, the code from your second post works great! I retried the code from
jim and dymondjack, with the bound TextBox gone, but that code didn't
work.

They both had the right idea, but made minor mistakes.

Out of curiosity, is there way to tell Access to disregard the TextBox
named
CurrentDate?

In a loop like that, you mean? Sure, just test the control's Name property:

For Each C In Me.Controls
If TypeOf C Is Access.TextBox _
Or TypeOf C Is Access.ComboBox _
Then

If C.Name <> "CurrentDate" Then
C = Null
End If

End If
Next C

 

[ryguy7272]

I think I tried something like this:
If CurrentDate.Value <> "" Then

That totally didn't work.

Your solution is beautiful!
BEAUTIFUL!!!!!!

Thanks so much Dirk!
Ryan-----