VBA to clear controls

[Accessor]

Hi all. I'm trying to write a general procedure that I can pass a form name
to to clear all text, list and combo boxes. I can't seem to pass the form as
a form, but that's another conversation. It works when I pass the form name
as a string, but I get "you can't assign a value to this object" on the first
text box. Any ideas? Here's my call:

Call ControlsClear("frmFreelanceLog")

And my sub:

Sub ControlsClear(frm As String)
'Clear all text boxes, list boxes, and combo boxes on the form passed.

Dim ctl As Control

For Each ctl In Forms(frm).Controls
If ctl.ControlType = acTextBox Then
ctl = ""
End If
Next ctl

End Sub

Thanks in advance...

 

[David H]

You'll need to change the declaration from (frm as String) to (frm as Form)
or see further below for another option...

Hi all. I'm trying to write a general procedure that I can pass a form name
to to clear all text, list and combo boxes. I can't seem to pass the form as
a form, but that's another conversation. It works when I pass the form name
as a string, but I get "you can't assign a value to this object" on the first
text box. Any ideas? Here's my call:

Call ControlsClear("frmFreelanceLog")

And my sub:

Sub ControlsClear(frm As String)

'Change this to (frmName as String)

'Clear all text boxes, list boxes, and combo boxes on the form passed.

'Add
' Dim frm as Form
'Get a reference to the form, you'll want to add a check here to confirm
that the form is a member of the forms collection or you'll get an error
Set frm = Forms(frmName) 'I changed the parameter from frm to
frmName for clarity

Dim ctl As Control

'note that its now frm.Controls

For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
ctl = ""
End If
Next ctl

'Close what you open, destroy what you create
'Set frm = Nothing

 

[AccessVandal via AccessMonster.com]

Actually, (frm As String) is alright, you did not set the object to the form.

Sub ControlsClear(frm As String)
'Clear all text boxes, list boxes, and combo boxes on the form passed.

Dim ctl As Control
Dim MyFrm As Form

Set MyFrm = Form(frm).Form 'set the object

For Each ctl In MyFrm
If ctl.ControlType = acTextBox Then
ctl = ""
End If
Next ctl

set ctl = nothing
set MyFrm = nothing
End Sub

 

[Accessor]

Thanks for both options!!

Actually, (frm As String) is alright, you did not set the object to the form.

Sub ControlsClear(frm As String)
'Clear all text boxes, list boxes, and combo boxes on the form passed.

Dim ctl As Control
Dim MyFrm As Form

Set MyFrm = Form(frm).Form 'set the object

For Each ctl In MyFrm
If ctl.ControlType = acTextBox Then
ctl = ""
End If
Next ctl

set ctl = nothing
set MyFrm = nothing
End Sub