Find First Blank Control

[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

[Barry Edmund Wright]

Tom put this code in a button on your form.
I tested it, would place the cursor on the first empty text box.

Dim ctl As Control
Dim vIndex As Long

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
With ctl
If IsNull(Me.Controls.Item(vIndex)) = True Then
MsgBox vIndex & ": " & Me.Controls.Item(vIndex).Name & " Value: "
& Me.Controls.Item(vIndex)
Me.Controls.Item(vIndex).SetFocus
Exit Sub
End If
End With
End If
'This is used to track the actual Item Number.
vIndex = vIndex + 1
Next ctl

Regards
Barry

"Tom" <ts******@nospam.please> wrote in message
news:8A****************@newsread3.news.pas.earthli nk.net...

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

[John Nurick]

On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <ts******@nospam.please> wrote:

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

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
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[Tom]

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

"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:84********************************@4ax.com...

On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <ts******@nospam.please> wrote:

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

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
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[John Nurick]

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

On Fri, 07 Jan 2005 16:51:11 GMT, "Tom" <ts******@nospam.please> wrote:

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

"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:84********************************@4ax.com.. .

On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <ts******@nospam.please> wrote:

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

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
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

[Tom]

Thanks, John, for all your help especially the code here!

Tom

"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:77********************************@4ax.com...

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

On Fri, 07 Jan 2005 16:51:11 GMT, "Tom" <ts******@nospam.please> wrote:

Thanks, John!

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

Tom

"John Nurick" <j.*************@dial.pipex.com> wrote in message
news:84********************************@4ax.com.. .

On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <ts******@nospam.please> wrote:

>How can I find the first blank control on a form by looking at the

controls

>in the order of their tab order?

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
--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.