Find First Blank Control

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

re: Find First Blank Control

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" <tstewart@nospam.please> wrote in message
news:8AiDd.2415$v76.214@newsread3.news.pas.earthli nk.net...[color=blue]
> 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
>
>[/color]

re: Find First Blank Control

On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <tstewart@nospam.please> wrote:
[color=blue]
>How can I find the first blank control on a form by looking at the controls
>in the order of their tab order?[/color]

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.

re: Find First Blank Control

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.mapSoN.nurick@dial.pipex.com> wrote in message
news:84ast0pnqmhvbvobp7ntlc0csqfleq6pfp@4ax.com...[color=blue]
> On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <tstewart@nospam.please> wrote:
>[color=green]
> >How can I find the first blank control on a form by looking at the[/color][/color]
controls[color=blue][color=green]
> >in the order of their tab order?[/color]
>
> 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.[/color]

re: Find First Blank Control

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" <tstewart@nospam.please> wrote:
[color=blue]
>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.mapSoN.nurick@dial.pipex.com> wrote in message
>news:84ast0pnqmhvbvobp7ntlc0csqfleq6pfp@4ax.com.. .[color=green]
>> On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <tstewart@nospam.please> wrote:
>>[color=darkred]
>> >How can I find the first blank control on a form by looking at the[/color][/color]
>controls[color=green][color=darkred]
>> >in the order of their tab order?[/color]
>>
>> 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.[/color]
>[/color]

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

re: Find First Blank Control

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

Tom



"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
news:772vt0ll6vskh9n7bqjd735nllavkuj5du@4ax.com...[color=blue]
> 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" <tstewart@nospam.please> wrote:
>[color=green]
> >Thanks, John!
> >
> >I was still wondering how to get the controls in tab order. Where can I[/color][/color]
find[color=blue][color=green]
> >a list of the constants for each type of control?
> >
> >Tom
> >
> >
> >
> >"John Nurick" <j.mapSoN.nurick@dial.pipex.com> wrote in message
> >news:84ast0pnqmhvbvobp7ntlc0csqfleq6pfp@4ax.com.. .[color=darkred]
> >> On Thu, 06 Jan 2005 22:02:44 GMT, "Tom" <tstewart@nospam.please> wrote:
> >>
> >> >How can I find the first blank control on a form by looking at the[/color]
> >controls[color=darkred]
> >> >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.[/color]
> >[/color]
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.[/color]