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



--
RyGuy

Try just using:

Dim C As Control

"ryguy7272" wrote:

> 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---
>
>
>
> --
> RyGuy

"ryguy7272" <(E-Mail Removed)> wrote in message
news:683FD014-2497-4DA0-A166-(E-Mail Removed)...
>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 -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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


"ryguy7272" wrote:

> 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---
>
>
>
> --
> RyGuy

"ryguy7272" <(E-Mail Removed)> wrote in message
news:099E50B6-5867-4355-95CF-(E-Mail Removed)...
> 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 -----


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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
--
RyGuy


"Dirk Goldgar" wrote:

> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:099E50B6-5867-4355-95CF-(E-Mail Removed)...
> > 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 -----
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

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

--
RyGuy


"Dirk Goldgar" wrote:

> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:099E50B6-5867-4355-95CF-(E-Mail Removed)...
> > 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 -----
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>

"ryguy7272" <(E-Mail Removed)> wrote in message
news:E05ECB7C-2746-43A9-82BB-(E-Mail Removed)...
> 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


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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

--
RyGuy


"Dirk Goldgar" wrote:

> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:E05ECB7C-2746-43A9-82BB-(E-Mail Removed)...
> > 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
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>