Find First Blank Control

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

How can I find the first blank control on a form by looking at the controls
in the order of their tab order?

Thanks!

Tom
 
How can I find the first blank control on a form by looking at the controls
in the order of their tab order?
Click to expand...

Something like this:

1) Decide which types of control you're interested in. E.g. Labels and
Lines don't have a TabIndex property, and blank commandbuttons may or
may not be of interest.

2) For each type you're interested in, decide what you mean by "blank"
(e.g. Null, False, zero-length string?) What about subform controls?

3) Iterate through all the controls on the form.
For Each ctlC in Me.Controls
If ctlC.Type is one you interested in Then
store its Name and TabIndex in a structure or collection
End If
Next
Iterate through the stored control names in TabIndex order
Check the type of the control, apply your rule for
whether it's blank (e.g. for a textbox,
IsNull(Me.Controls(strCtlName).Value)
Stop at the first blank control or at the end of the form
 
Thanks, John!

I was still wondering how to get the controls in tab order. Where can I find
a list of the constants for each type of control?

Tom
 
Tom,

For the constants, look up ControlType in the Object Browser.

One way of getting them in tab order is along these lines:

Dim colC As New VBA.Collection
Dim j as Long
Dim lngHighestTabIndex As Long
...
lngHighestTabIndex = 0

'store relevant names and tab indexes in collection
For Each ctlC in Me.Controls
With ctlC
Select Case .Type
Case acBlah, acSomething, acSomethingElse...
colC.Add ctlC.Name, Format(.TabIndex, "000")
If .TabIndex > lngHighestTabIndex Then
lngHighestTabIndex = .TabIndex
End If
End Select
Next 'ctlC

'Iterate through the stored control names in TabIndex order
On Error Resume Next
For j = 1 To lngHighestTabIndex
strCtlName = colC.Item(Format(j, "000"))
If Err.Number = 0 Then
If [control is blank] Then
Exit For
End If
End If
Err.Clear
Next j
On Error GoTo 0



Thanks, John!

I was still wondering how to get the controls in tab order. Where can I find
a list of the constants for each type of control?

Tom
Click to expand...
 
Thanks, John, for all your help especially the code here!

Tom



John Nurick said:
Tom,

For the constants, look up ControlType in the Object Browser.

One way of getting them in tab order is along these lines:

Dim colC As New VBA.Collection
Dim j as Long
Dim lngHighestTabIndex As Long
...
lngHighestTabIndex = 0

'store relevant names and tab indexes in collection
For Each ctlC in Me.Controls
With ctlC
Select Case .Type
Case acBlah, acSomething, acSomethingElse...
colC.Add ctlC.Name, Format(.TabIndex, "000")
If .TabIndex > lngHighestTabIndex Then
lngHighestTabIndex = .TabIndex
End If
End Select
Next 'ctlC

'Iterate through the stored control names in TabIndex order
On Error Resume Next
For j = 1 To lngHighestTabIndex
strCtlName = colC.Item(Format(j, "000"))
If Err.Number = 0 Then
If [control is blank] Then
Exit For
End If
End If
Err.Clear
Next j
On Error GoTo 0
Click to expand...
 
Back
Top