Unbound data entry form

[Barbara]

I want to clear text boxes, check boxes and combo boxes on
an unfound form using control variables and a With Each
statement but don't know the complete With Each statment.
In other words, in the following example I have a variable
named ctlTextBoxes. My form is frmCandidates:

With Each ctlTextBox in ?????


Next ctlTextBox

Can someone fill in the ????? for me?

 

[Cameron Sutherland]

Here is a bit of code that will rotate through your form
and find every object on it. Currently it has a msgbox
output what type of object it found but you can change
that to edit that objects contents instead.
-Cameron Sutherland

Public Function CycleObjects(frm As Form, bolState As
Boolean)
Dim intObjectNumber As Integer 'array count of object
Dim intObjectTotal As Integer 'Total number of objects
on form (minus 1 since array)
'Count the number of controls
intObjectTotal = frm.Count
' Cycle through the form's controls,
' testing for text and combo boxes,
' and set each control's Locked/Enabled properties.
For intObjectNumber = 0 To intObjectTotal - 1
If TypeOf frm(intObjectNumber) Is CheckBox Then
ElseIf TypeOf frm(intObjectNumber) Is ComboBox Then
ElseIf TypeOf frm(intObjectNumber) Is
CommandButton Then
ElseIf TypeOf frm(intObjectNumber) Is Image Then
ElseIf TypeOf frm(intObjectNumber) Is Label Then
ElseIf TypeOf frm(intObjectNumber) Is Line Then
ElseIf TypeOf frm(intObjectNumber) Is ObjectFrame
Then
ElseIf TypeOf frm(intObjectNumber) Is OptionButton
Then
ElseIf TypeOf frm(intObjectNumber) Is OptionGroup
Then
ElseIf TypeOf frm(intObjectNumber) Is Rectangle
Then
ElseIf TypeOf frm(intObjectNumber) Is SubForm Then
ElseIf TypeOf frm(intObjectNumber) Is SubReport
Then
ElseIf TypeOf frm(intObjectNumber) Is TabControl
Then
ElseIf TypeOf frm(intObjectNumber) Is TextBox Then
ElseIf TypeOf frm(intObjectNumber) Is ToggleButton
Then
End If

'Use ControlType to determine the Type of Control
Select Case frm(intObjectNumber).ControlType
Case acLabel
MsgBox "Label"
Case acRectangle
MsgBox "Rectangle"
Case acLine
MsgBox "Line"
Case acImage
MsgBox "Image"
Case acCommandButton
MsgBox "Command Button"
Case acOptionButton
MsgBox "Option button"
Case acCheckBox
MsgBox "Check box"
Case acOptionGroup
MsgBox "Option group"
Case acBoundObjectFrame
MsgBox "Bound object frame"
Case acTextBox
MsgBox "Text Box"
Case acListBox
MsgBox "List box"
Case acComboBox
MsgBox "Combo box"
Case acSubform
MsgBox "SubForm"
Case acObjectFrame
MsgBox "Unbound object frame or chart"
Case acPageBreak
MsgBox "Page break"
Case acPage
MsgBox "Page"
Case acCustomControl
MsgBox "ActiveX (custom) control"
Case acToggleButton
MsgBox "Toggle Button"
Case acTabCtl
MsgBox "Tab Control"
End Select
Next intObjectNumber
End Function

 

[Matthew Sullivan]

Something like the below?
(Warning: half-tested air code.)

----------------------------------
Private Sub ClearControls()

'declare variables
Dim theControl As Access.Control
Dim theForm As Access.Form

'instantiate objects
Set theForm = Me '(note: make this a
' parameter, for
' maximum flexibility)


'loop through the controls
For Each theControl In theForm.Controls

Select Case theControl.Properties("ControlType")

Case acLabel
'do nothing

Case acComboBox, _
acTextBox
theControl.Value = Null

Case Else
msgBox "Unexpected control type: " & _
theControl.Properties("ControlType") & _
vbNewLine & vbNewLine & _
"Control Name: " & theControl.Name

End Select

Next theControl


'free resources
Set theControl = Nothing
Set theForm = Nothing

End Sub