VBA to clear controls

A

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

David H

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

Accessor said:
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
 
A

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
 
A

Accessor

Thanks for both options!!

AccessVandal via AccessMonster.com said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top