Checking controls through module code in Access 97 
Author Message
 Checking controls through module code in Access 97

Hello, all . . .

I have a program that uses a number of data input forms--each with its own
number of text and/or combo boxes.  Instead of building code in each form to
check for null values, display a message, and then set focus to the null
value, I am trying to use a generic code in a module to handle this.  I have
managed to accurately check for the values, display the message, and set
focus; however, for some reason it does not set the focus to the first blank
value.  Instead it sets focus to a control that is also null, but whose tab
order is later.  For instance, assume a form with six fields, all of which
are null.  Instead of setting focus to the first field (tab order 0), it
selects the third, second, and first in that order followed by number six,
five and four.  While it is true that each is null, I'd rather the flow be
in topdown structure.  Attached is the code, so if anyone feels that they
can help me on this, I would be appreciative.

Thanks in advance,

***Please note that this is posted to two newsgroups--modulesdaovba and
formscoding***

*************Begin Code**********************
Public Sub Check_Null_Controls(frmActiveForm As Form)

     Dim ctlActiveControls As Control
     Dim strMsg As String, strTitle As String
     Dim intStyle As Integer

     strMsg = "You cannot leave this field blank." & Chr(13) & Chr(13) &
"Please enter the missing information."
     strTitle = "Missing Data"
     intStyle = vbOKOnly + vbInformation

     'Loops through all data entry controls on the active form
     For Each ctlActiveControls In frmActiveForm.Controls
          If ((ctlActiveControls.ControlType = acTextBox) Or
(ctlActiveControls.ControlType = acComboBox)) Then
                If IsNull(ctlActiveControls) Then
                    MsgBox strMsg, intStyle, strTitle
                    ctlActiveControls.SetFocus
                    GoTo Exit_Check_Null_Controls
                End If
          End If
     Next ctlActiveControls
Exit_Check_Null_Controls:
     Exit Sub

End Sub

*********************End Code*******************
--
Edward Lollar
Programmer / Analyst
MS State Tax Commission



Sun, 09 Jan 2005 04:09:40 GMT  
 Checking controls through module code in Access 97
Hi Edward,
The order in which the code processes the controls
will have nothing to do with the tab order you have set.

If I'm not mistaken, it will be the order in which the
controls were created. (but I'm not 100% sure)

Okay, I just did a quick test and Access does loop
through in the order they were created.

In any case I'm not sure how you force the code
to loop through the collection in the tab order
you've specified.

This was such an interesting problem, I had to come up with something.

So here is my solution. For simplicity I put the code in the
form module so you'll have to modify the references to use
your function. Also, I took out your Null check and
just popped up a MsgBox to indicate they were being
done in the right order.

In my limited tests, this will process the controls by
the tab order you have set:

 Dim ctlActiveControls As Control
     Dim strMsg As String, strTitle As String
     Dim intStyle As Integer
     Dim intctlCount As Integer
     Dim i As Integer

     On Error GoTo Detail_Err

     i = 0

     intctlCount = Me.Controls.Count

Do While i <> intctlCount
 For Each ctlActiveControls In Me.Controls
   If ctlActiveControls.TabIndex = i Then
          If ((ctlActiveControls.ControlType = acTextBox) Or _
          (ctlActiveControls.ControlType = acComboBox)) Then
                MsgBox ctlActiveControls.Name & " " & ctlActiveControls.TabIndex
          End If
    End If
 Next ctlActiveControls
 i = i + 1
Loop

Detail_Err:
     If Err.Number = 438 Then
        Resume Next
    End If

HTH
Dan Artuso, MVP

Quote:

> Hello, all . . .

> I have a program that uses a number of data input forms--each with its own
> number of text and/or combo boxes.  Instead of building code in each form to
> check for null values, display a message, and then set focus to the null
> value, I am trying to use a generic code in a module to handle this.  I have
> managed to accurately check for the values, display the message, and set
> focus; however, for some reason it does not set the focus to the first blank
> value.  Instead it sets focus to a control that is also null, but whose tab
> order is later.  For instance, assume a form with six fields, all of which
> are null.  Instead of setting focus to the first field (tab order 0), it
> selects the third, second, and first in that order followed by number six,
> five and four.  While it is true that each is null, I'd rather the flow be
> in topdown structure.  Attached is the code, so if anyone feels that they
> can help me on this, I would be appreciative.

> Thanks in advance,

> ***Please note that this is posted to two newsgroups--modulesdaovba and
> formscoding***

> *************Begin Code**********************
> Public Sub Check_Null_Controls(frmActiveForm As Form)

>      Dim ctlActiveControls As Control
>      Dim strMsg As String, strTitle As String
>      Dim intStyle As Integer

>      strMsg = "You cannot leave this field blank." & Chr(13) & Chr(13) &
> "Please enter the missing information."
>      strTitle = "Missing Data"
>      intStyle = vbOKOnly + vbInformation

>      'Loops through all data entry controls on the active form
>      For Each ctlActiveControls In frmActiveForm.Controls
>           If ((ctlActiveControls.ControlType = acTextBox) Or
> (ctlActiveControls.ControlType = acComboBox)) Then
>                 If IsNull(ctlActiveControls) Then
>                     MsgBox strMsg, intStyle, strTitle
>                     ctlActiveControls.SetFocus
>                     GoTo Exit_Check_Null_Controls
>                 End If
>           End If
>      Next ctlActiveControls
> Exit_Check_Null_Controls:
>      Exit Sub

> End Sub

> *********************End Code*******************
> --
> Edward Lollar
> Programmer / Analyst
> MS State Tax Commission



Sun, 09 Jan 2005 09:52:15 GMT  
 Checking controls through module code in Access 97
Dan,
Why not just loop through the controls once and get the index of the control
that is null and has the lowest tab number?

Pseudo Code

For iCount = 0 to Controls.Count -1

If ((ctlActiveControls.ControlType = acTextBox) Or _

Quote:
>           (ctlActiveControls.ControlType = acComboBox)) Then
>                 MsgBox ctlActiveControls.Name & " " & ctlActiveControls.TabIndex
>           End If

Next iCount
Quote:

> Hi Edward,
> The order in which the code processes the controls
> will have nothing to do with the tab order you have set.

> If I'm not mistaken, it will be the order in which the
> controls were created. (but I'm not 100% sure)

> Okay, I just did a quick test and Access does loop
> through in the order they were created.

> In any case I'm not sure how you force the code
> to loop through the collection in the tab order
> you've specified.

> This was such an interesting problem, I had to come up with something.

> So here is my solution. For simplicity I put the code in the
> form module so you'll have to modify the references to use
> your function. Also, I took out your Null check and
> just popped up a MsgBox to indicate they were being
> done in the right order.

> In my limited tests, this will process the controls by
> the tab order you have set:

>  Dim ctlActiveControls As Control
>      Dim strMsg As String, strTitle As String
>      Dim intStyle As Integer
>      Dim intctlCount As Integer
>      Dim i As Integer

>      On Error GoTo Detail_Err

>      i = 0

>      intctlCount = Me.Controls.Count

> Do While i <> intctlCount
>  For Each ctlActiveControls In Me.Controls
>    If ctlActiveControls.TabIndex = i Then
>           If ((ctlActiveControls.ControlType = acTextBox) Or _
>           (ctlActiveControls.ControlType = acComboBox)) Then
>                 MsgBox ctlActiveControls.Name & " " & ctlActiveControls.TabIndex
>           End If
>     End If
>  Next ctlActiveControls
>  i = i + 1
> Loop

> Detail_Err:
>      If Err.Number = 438 Then
>         Resume Next
>     End If

> HTH
> Dan Artuso, MVP


> > Hello, all . . .

> > I have a program that uses a number of data input forms--each with its own
> > number of text and/or combo boxes.  Instead of building code in each form to
> > check for null values, display a message, and then set focus to the null
> > value, I am trying to use a generic code in a module to handle this.  I have
> > managed to accurately check for the values, display the message, and set
> > focus; however, for some reason it does not set the focus to the first blank
> > value.  Instead it sets focus to a control that is also null, but whose tab
> > order is later.  For instance, assume a form with six fields, all of which
> > are null.  Instead of setting focus to the first field (tab order 0), it
> > selects the third, second, and first in that order followed by number six,
> > five and four.  While it is true that each is null, I'd rather the flow be
> > in topdown structure.  Attached is the code, so if anyone feels that they
> > can help me on this, I would be appreciative.

> > Thanks in advance,

> > ***Please note that this is posted to two newsgroups--modulesdaovba and
> > formscoding***

> > *************Begin Code**********************
> > Public Sub Check_Null_Controls(frmActiveForm As Form)

> >      Dim ctlActiveControls As Control
> >      Dim strMsg As String, strTitle As String
> >      Dim intStyle As Integer

> >      strMsg = "You cannot leave this field blank." & Chr(13) & Chr(13) &
> > "Please enter the missing information."
> >      strTitle = "Missing Data"
> >      intStyle = vbOKOnly + vbInformation

> >      'Loops through all data entry controls on the active form
> >      For Each ctlActiveControls In frmActiveForm.Controls
> >           If ((ctlActiveControls.ControlType = acTextBox) Or
> > (ctlActiveControls.ControlType = acComboBox)) Then
> >                 If IsNull(ctlActiveControls) Then
> >                     MsgBox strMsg, intStyle, strTitle
> >                     ctlActiveControls.SetFocus
> >                     GoTo Exit_Check_Null_Controls
> >                 End If
> >           End If
> >      Next ctlActiveControls
> > Exit_Check_Null_Controls:
> >      Exit Sub

> > End Sub

> > *********************End Code*******************
> > --
> > Edward Lollar
> > Programmer / Analyst
> > MS State Tax Commission



Sun, 09 Jan 2005 19:24:50 GMT  
 Checking controls through module code in Access 97
Thanks to both of you for your input.  This was the solution to my problem,
and it's working fine now.

Regards,

Edward Lollar
Programmer / Analyst
MS State Tax Commission



Quote:
> Dan,
> Why not just loop through the controls once and get the index of the
control
> that is null and has the lowest tab number?

> Pseudo Code

> For iCount = 0 to Controls.Count -1

> If ((ctlActiveControls.ControlType = acTextBox) Or _
> >           (ctlActiveControls.ControlType = acComboBox)) Then
> >                 MsgBox ctlActiveControls.Name & " " &

ctlActiveControls.TabIndex
Quote:
> >           End If

> Next iCount


> > Hi Edward,
> > The order in which the code processes the controls
> > will have nothing to do with the tab order you have set.

> > If I'm not mistaken, it will be the order in which the
> > controls were created. (but I'm not 100% sure)

> > Okay, I just did a quick test and Access does loop
> > through in the order they were created.

> > In any case I'm not sure how you force the code
> > to loop through the collection in the tab order
> > you've specified.

> > This was such an interesting problem, I had to come up with something.

> > So here is my solution. For simplicity I put the code in the
> > form module so you'll have to modify the references to use
> > your function. Also, I took out your Null check and
> > just popped up a MsgBox to indicate they were being
> > done in the right order.

> > In my limited tests, this will process the controls by
> > the tab order you have set:

> >  Dim ctlActiveControls As Control
> >      Dim strMsg As String, strTitle As String
> >      Dim intStyle As Integer
> >      Dim intctlCount As Integer
> >      Dim i As Integer

> >      On Error GoTo Detail_Err

> >      i = 0

> >      intctlCount = Me.Controls.Count

> > Do While i <> intctlCount
> >  For Each ctlActiveControls In Me.Controls
> >    If ctlActiveControls.TabIndex = i Then
> >           If ((ctlActiveControls.ControlType = acTextBox) Or _
> >           (ctlActiveControls.ControlType = acComboBox)) Then
> >                 MsgBox ctlActiveControls.Name & " " &

ctlActiveControls.TabIndex

- Show quoted text -

Quote:
> >           End If
> >     End If
> >  Next ctlActiveControls
> >  i = i + 1
> > Loop

> > Detail_Err:
> >      If Err.Number = 438 Then
> >         Resume Next
> >     End If

> > HTH
> > Dan Artuso, MVP


> > > Hello, all . . .

> > > I have a program that uses a number of data input forms--each with its
own
> > > number of text and/or combo boxes.  Instead of building code in each
form to
> > > check for null values, display a message, and then set focus to the
null
> > > value, I am trying to use a generic code in a module to handle this.
I have
> > > managed to accurately check for the values, display the message, and
set
> > > focus; however, for some reason it does not set the focus to the first
blank
> > > value.  Instead it sets focus to a control that is also null, but
whose tab
> > > order is later.  For instance, assume a form with six fields, all of
which
> > > are null.  Instead of setting focus to the first field (tab order 0),
it
> > > selects the third, second, and first in that order followed by number
six,
> > > five and four.  While it is true that each is null, I'd rather the
flow be
> > > in topdown structure.  Attached is the code, so if anyone feels that
they
> > > can help me on this, I would be appreciative.

> > > Thanks in advance,

> > > ***Please note that this is posted to two newsgroups--modulesdaovba
and
> > > formscoding***

> > > *************Begin Code**********************
> > > Public Sub Check_Null_Controls(frmActiveForm As Form)

> > >      Dim ctlActiveControls As Control
> > >      Dim strMsg As String, strTitle As String
> > >      Dim intStyle As Integer

> > >      strMsg = "You cannot leave this field blank." & Chr(13) & Chr(13)
&
> > > "Please enter the missing information."
> > >      strTitle = "Missing Data"
> > >      intStyle = vbOKOnly + vbInformation

> > >      'Loops through all data entry controls on the active form
> > >      For Each ctlActiveControls In frmActiveForm.Controls
> > >           If ((ctlActiveControls.ControlType = acTextBox) Or
> > > (ctlActiveControls.ControlType = acComboBox)) Then
> > >                 If IsNull(ctlActiveControls) Then
> > >                     MsgBox strMsg, intStyle, strTitle
> > >                     ctlActiveControls.SetFocus
> > >                     GoTo Exit_Check_Null_Controls
> > >                 End If
> > >           End If
> > >      Next ctlActiveControls
> > > Exit_Check_Null_Controls:
> > >      Exit Sub

> > > End Sub

> > > *********************End Code*******************
> > > --
> > > Edward Lollar
> > > Programmer / Analyst
> > > MS State Tax Commission



Sun, 09 Jan 2005 23:04:36 GMT  
 Checking controls through module code in Access 97
Thanks to both of you for your input.  This was the solution to my problem,
and it's working fine now.

Regards,

Edward Lollar
Programmer / Analyst
MS State Tax Commission



Quote:
> Dan,
> Why not just loop through the controls once and get the index of the
control
> that is null and has the lowest tab number?

> Pseudo Code

> For iCount = 0 to Controls.Count -1

> If ((ctlActiveControls.ControlType = acTextBox) Or _
> >           (ctlActiveControls.ControlType = acComboBox)) Then
> >                 MsgBox ctlActiveControls.Name & " " &

ctlActiveControls.TabIndex
Quote:
> >           End If

> Next iCount


> > Hi Edward,
> > The order in which the code processes the controls
> > will have nothing to do with the tab order you have set.

> > If I'm not mistaken, it will be the order in which the
> > controls were created. (but I'm not 100% sure)

> > Okay, I just did a quick test and Access does loop
> > through in the order they were created.

> > In any case I'm not sure how you force the code
> > to loop through the collection in the tab order
> > you've specified.

> > This was such an interesting problem, I had to come up with something.

> > So here is my solution. For simplicity I put the code in the
> > form module so you'll have to modify the references to use
> > your function. Also, I took out your Null check and
> > just popped up a MsgBox to indicate they were being
> > done in the right order.

> > In my limited tests, this will process the controls by
> > the tab order you have set:

> >  Dim ctlActiveControls As Control
> >      Dim strMsg As String, strTitle As String
> >      Dim intStyle As Integer
> >      Dim intctlCount As Integer
> >      Dim i As Integer

> >      On Error GoTo Detail_Err

> >      i = 0

> >      intctlCount = Me.Controls.Count

> > Do While i <> intctlCount
> >  For Each ctlActiveControls In Me.Controls
> >    If ctlActiveControls.TabIndex = i Then
> >           If ((ctlActiveControls.ControlType = acTextBox) Or _
> >           (ctlActiveControls.ControlType = acComboBox)) Then
> >                 MsgBox ctlActiveControls.Name & " " &

ctlActiveControls.TabIndex

- Show quoted text -

Quote:
> >           End If
> >     End If
> >  Next ctlActiveControls
> >  i = i + 1
> > Loop

> > Detail_Err:
> >      If Err.Number = 438 Then
> >         Resume Next
> >     End If

> > HTH
> > Dan Artuso, MVP


> > > Hello, all . . .

> > > I have a program that uses a number of data input forms--each with its
own
> > > number of text and/or combo boxes.  Instead of building code in each
form to
> > > check for null values, display a message, and then set focus to the
null
> > > value, I am trying to use a generic code in a module to handle this.
I have
> > > managed to accurately check for the values, display the message, and
set
> > > focus; however, for some reason it does not set the focus to the first
blank
> > > value.  Instead it sets focus to a control that is also null, but
whose tab
> > > order is later.  For instance, assume a form with six fields, all of
which
> > > are null.  Instead of setting focus to the first field (tab order 0),
it
> > > selects the third, second, and first in that order followed by number
six,
> > > five and four.  While it is true that each is null, I'd rather the
flow be
> > > in topdown structure.  Attached is the code, so if anyone feels that
they
> > > can help me on this, I would be appreciative.

> > > Thanks in advance,

> > > ***Please note that this is posted to two newsgroups--modulesdaovba
and
> > > formscoding***

> > > *************Begin Code**********************
> > > Public Sub Check_Null_Controls(frmActiveForm As Form)

> > >      Dim ctlActiveControls As Control
> > >      Dim strMsg As String, strTitle As String
> > >      Dim intStyle As Integer

> > >      strMsg = "You cannot leave this field blank." & Chr(13) & Chr(13)
&
> > > "Please enter the missing information."
> > >      strTitle = "Missing Data"
> > >      intStyle = vbOKOnly + vbInformation

> > >      'Loops through all data entry controls on the active form
> > >      For Each ctlActiveControls In frmActiveForm.Controls
> > >           If ((ctlActiveControls.ControlType = acTextBox) Or
> > > (ctlActiveControls.ControlType = acComboBox)) Then
> > >                 If IsNull(ctlActiveControls) Then
> > >                     MsgBox strMsg, intStyle, strTitle
> > >                     ctlActiveControls.SetFocus
> > >                     GoTo Exit_Check_Null_Controls
> > >                 End If
> > >           End If
> > >      Next ctlActiveControls
> > > Exit_Check_Null_Controls:
> > >      Exit Sub

> > > End Sub

> > > *********************End Code*******************
> > > --
> > > Edward Lollar
> > > Programmer / Analyst
> > > MS State Tax Commission



Sun, 09 Jan 2005 23:05:04 GMT  
 Checking controls through module code in Access 97
Hi John,
'cause I didn't think of it? <g>

Sometimes my mind goes down one path and I just
follow it! (until someone point out a more efficient way)

Dan

Quote:

> Dan,
> Why not just loop through the controls once and get the index of the control
> that is null and has the lowest tab number?

> Pseudo Code

> For iCount = 0 to Controls.Count -1

> If ((ctlActiveControls.ControlType = acTextBox) Or _
> >           (ctlActiveControls.ControlType = acComboBox)) Then
> >                 MsgBox ctlActiveControls.Name & " " & ctlActiveControls.TabIndex
> >           End If

> Next iCount


> > Hi Edward,
> > The order in which the code processes the controls
> > will have nothing to do with the tab order you have set.

> > If I'm not mistaken, it will be the order in which the
> > controls were created. (but I'm not 100% sure)

> > Okay, I just did a quick test and Access does loop
> > through in the order they were created.

> > In any case I'm not sure how you force the code
> > to loop through the collection in the tab order
> > you've specified.

> > This was such an interesting problem, I had to come up with something.

> > So here is my solution. For simplicity I put the code in the
> > form module so you'll have to modify the references to use
> > your function. Also, I took out your Null check and
> > just popped up a MsgBox to indicate they were being
> > done in the right order.

> > In my limited tests, this will process the controls by
> > the tab order you have set:

> >  Dim ctlActiveControls As Control
> >      Dim strMsg As String, strTitle As String
> >      Dim intStyle As Integer
> >      Dim intctlCount As Integer
> >      Dim i As Integer

> >      On Error GoTo Detail_Err

> >      i = 0

> >      intctlCount = Me.Controls.Count

> > Do While i <> intctlCount
> >  For Each ctlActiveControls In Me.Controls
> >    If ctlActiveControls.TabIndex = i Then
> >           If ((ctlActiveControls.ControlType = acTextBox) Or _
> >           (ctlActiveControls.ControlType = acComboBox)) Then
> >                 MsgBox ctlActiveControls.Name & " " & ctlActiveControls.TabIndex
> >           End If
> >     End If
> >  Next ctlActiveControls
> >  i = i + 1
> > Loop

> > Detail_Err:
> >      If Err.Number = 438 Then
> >         Resume Next
> >     End If

> > HTH
> > Dan Artuso, MVP


> > > Hello, all . . .

> > > I have a program that uses a number of data input forms--each with its own
> > > number of text and/or combo boxes.  Instead of building code in each form to
> > > check for null values, display a message, and then set focus to the null
> > > value, I am trying to use a generic code in a module to handle this.  I have
> > > managed to accurately check for the values, display the message, and set
> > > focus; however, for some reason it does not set the focus to the first blank
> > > value.  Instead it sets focus to a control that is also null, but whose tab
> > > order is later.  For instance, assume a form with six fields, all of which
> > > are null.  Instead of setting focus to the first field (tab order 0), it
> > > selects the third, second, and first in that order followed by number six,
> > > five and four.  While it is true that each is null, I'd rather the flow be
> > > in topdown structure.  Attached is the code, so if anyone feels that they
> > > can help me on this, I would be appreciative.

> > > Thanks in advance,

> > > ***Please note that this is posted to two newsgroups--modulesdaovba and
> > > formscoding***

> > > *************Begin Code**********************
> > > Public Sub Check_Null_Controls(frmActiveForm As Form)

> > >      Dim ctlActiveControls As Control
> > >      Dim strMsg As String, strTitle As String
> > >      Dim intStyle As Integer

> > >      strMsg = "You cannot leave this field blank." & Chr(13) & Chr(13) &
> > > "Please enter the missing information."
> > >      strTitle = "Missing Data"
> > >      intStyle = vbOKOnly + vbInformation

> > >      'Loops through all data entry controls on the active form
> > >      For Each ctlActiveControls In frmActiveForm.Controls
> > >           If ((ctlActiveControls.ControlType = acTextBox) Or
> > > (ctlActiveControls.ControlType = acComboBox)) Then
> > >                 If IsNull(ctlActiveControls) Then
> > >                     MsgBox strMsg, intStyle, strTitle
> > >                     ctlActiveControls.SetFocus
> > >                     GoTo Exit_Check_Null_Controls
> > >                 End If
> > >           End If
> > >      Next ctlActiveControls
> > > Exit_Check_Null_Controls:
> > >      Exit Sub

> > > End Sub

> > > *********************End Code*******************
> > > --
> > > Edward Lollar
> > > Programmer / Analyst
> > > MS State Tax Commission



Mon, 10 Jan 2005 08:22:24 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Creating new Access 97 database by VB code in Access 2000 module

2. Converting VB code modules in Access 95 to Access 97

3. Vba code which can execute FTP script from within the Access 97 module

4. Creating Modules in Access 97 using vba code

5. Need help coding simple module in Access 97

6. Converting Access 2.0 modules to Access 97

7. Accessing the modules of an Access 97 database.

8. Accessing the modules of an Access 97 database.

9. Accessing the modules of an Access 97 database.

10. Access 97 and Source Code Control - Necessary?

11. vba code to copy table from access 97 to excel 97

12. Converting VB Code from Access 97 to Access 2002 or higher

 

 
Powered by phpBB® Forum Software