Solved

Ordering the Controls Collection

Posted on 2011-09-05
36
777 Views
Last Modified: 2012-05-12
I'm using

    For Each ctl In frm.Controls

to validate data.

Is there a way to order the Controls by Tab order?

I realise, of course, that some controls like labels will not have a tab order but They are irrelevant to this function.
0
Comment
Question by:kentgorrell
  • 10
  • 7
  • 7
  • +3
36 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

the order that the control will be accessed is the order in which the control was placed in the form.

* first control that was placed in the form will be the one that will be addressed first, and the last control that was placed to the form will be the last to be addressed, using  { For Each ctl In frm.Controls }


       
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
I don't think so, not easily anyway.

Are you trying to change the tab order of the controls?

The easiest way to do that is in design view.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
so the answer to your question
<Is there a way to order the Controls by Tab order?>  NO
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
0
 

Author Comment

by:kentgorrell
Comment Utility
Capricorn1 - looping through each control for a predetermined count is not quite right. The function would have to know how many controls and then it's a lot of looping.

But close.

Probably better to iterate through all the controls once and stuff the ones I want into a collection with the tab order then iterate through the resulting collection to do the actual work.

Or maybe write the form name, control Name and Tab Number into a table at design time. But I don't like this option because it means I have to remember to do it.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
What are you trying to do?

Why do you need the controls in a particular order?

Is using the tab order for that suitable for the task?

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
My understanding of your request is that you have a form with a tab order defining the sequence in which the user enters data.  In some form event (probably the Before Update event), you have validation checks for this input.  You (and your users) want these checks and related messages to be performed in the order in which they enter data for consistency, so the order of the checks has to be based on the Tab Order.

This is definitely doable.  One approach is to loop through all of the controls on the form, checking whether it's tab index is 1, repeat for a tab index of 2,  repeat for a tab index of 3, etc.  This will do the trick:

Private Sub SomeCommandButton_Click()
    Dim ctl As Control
    Dim I As Integer
    On Error GoTo EH
   
    ' Outer loop defines the order based on TabIndex
    For I = 1 To Me.Controls.Count
    ' Inner loop checks the control against the TabIndex determined by the outer loop
    For Each ctl In Me.Controls
        ' This checks the control against the tab Index, and defines the control types you are checking
        If ctl.TabIndex = I And (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox) Then
            ' Print the control name.  **** Your Validation checks would go here ****
            Debug.Print ctl.Name & "     " & ctl.TabIndex
        End If
    Next
    Next
    Exit Sub
EH:
   ' This handles 'property not found' errors for controls that don't have a TabIndex property
    If Err.Number = 438 Then Resume Next
    MsgBox "ERROR " & Err.Number & ": " & Err.Description
End Sub

Open in new window


You could add validation checks where indicated.

The code uses the "Me." prefix, so as is, it will run from the same form as the controls you are checking.  For a more generic solution, you would write this as a function which accepts a form name as a parameter, and use that instead of "Me."
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Hmmm that is actually very similar to what was posted at the link that cap1 posted.  

<iterate through all the controls once and stuff the ones I want into a collection with the tab order then iterate through the resulting collection to do the actual work.>

You COULD put these controls in order into an array, and then do validation checks based on that array, but it seems about as efficient (or inefficient) either way.  The validation checks don't have to be perfomed on each iteration of the loop; they are only performed under certain criteria.  And either way, you would need a similar loop to determine which order the controls go in.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
if you really want to get the controls in the order, they appear in your form..
there is  a way without reconstructing your form but it will be tedious..
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Perhaps I am mis-understanding something....

But if you are running a basic control loop...
For each ctl in me.controls...

...What relevance does the order or the controls have?

I mean, the code just does something for all the controls and is finished.
Basically the code will run so fast that you won't even notice the order...

Again, I am just curios as to why the tab order in a control loop would matter...?

JeffCoachman
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Jeff

I think what's wanted is a way to loop in some order when doing validation.

Not sure why though.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Yeah, that is my question

If you are looping the controls via code, the "Order" is irrelevant.

So my question is *why* (with a specific example) is the order of the loop needing to be set...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
kentgorrell,

Again, as was asked by imnorie and myself...

Can you take a moment to explain why the order of the conrols in the loop needs to be set?
Giving a specific example.

Just to make sure we are all on the same page...

I mean if you are looping the controls and changing something, the order really should not matter, because all the specified changes will be made...

Am I missing something here?

JeffCoachman
0
 

Author Comment

by:kentgorrell
Comment Utility
Sorry to take so long to get back to you. Only just morning here now.

The procedure is doing validation so the order is important. Very odd for the user if they are alerted to a control well down the form, they fix that, and then get taken to a control at the top.

The code above results in Controls.Count squared.. If there are lot of controls then the time taken can get a bit long.On a form with just 100 controls that is 10,000 loops

What I'm suggesting is looping through all controls once, to get the relevant controls, sort them into the correct order and then only operate on the twenty controls that need to be validated.
one large loop, one small loop. Same form just 120 loops.

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Then the wider question would be how is your interface designed to allow 100 controls to all need validation at the same time and in the same manner?
In other words, if it is possible for 100 controls to need validation, then you have to think about the interface...
No criticism here, it's just that a question like this has never come up in my memory.

For example.:
1. You must have a Quantity in order to get the extended price.
Set a default value of 1 for the Quantity field, so that even if the use forgets, there won't be a null.

2. The price must be below 1000.
You can set a validation rule in the Field properties to <1000
Or you can validate this via code on the before update event of the *control*

3. The dated entered must be valid
If the field is defined as a date validaton will kick in automatically if Access can't resolve the date
You can use a Date Picker, of the built in datepicker

4. The Order Date must be before the ship date
This can be done with "Table Validation"

I mean, ...Perhaps it's just me, but I am just having a tough time visualizing a situation where 100 fields would need the exact same validation.

In your situation it appears that the loop will perform the same "test"
So, for the information of all the Experts involved, ...can you take a step back and first explain, ...using a real world user scenario, why this needs to be done in this fashion?
(ex.: we have a form of 100_____.  They represent________.  We need this in order to_______.  A user must be sure that_________ before_________, therefore I need to______)

...Perhaps there is another approach.

JeffCoachman
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
kentgorrell,

read again my posts

1. http:#a36486242
2. http:#a36486252
3. http:#a36488131
0
 

Author Comment

by:kentgorrell
Comment Utility
Jeff, As I said, only about 20 need validation but to get at those you need to iterate through the entire controls collection to identify those 20 controls. Obviously I'm not interested in Labels and buttons that are in the controls collection.

capricorn1 - reread them and they still say the same thing as the last time I read them.

So back to my last conclusion -
Probably better to iterate through all the controls once and stuff the ones I want into a collection (or Array) with the tab order then iterate through the resulting collection just once to do the actual work.

Anyone got a good example of building an array and then reading out of it in Tab Order?
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
so, just to be clear, you are not interested regarding the way to do this as i posted in http:#a36488131
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:kentgorrell
Comment Utility
is this the code you mean?
For i = 0 To 9
For Each ctl In Me.Controls
    If ctl.ControlType = acComboBox Or ctl.ControlType = acTextBox Then
    If Me(ctl.Name).TabIndex = i Then
...

0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
This loops through the controls, placing them in an array according to the tab index of the controls:

Private Sub cmdOrderControls_Click()
    Dim ctl As Control
    Dim I, intMaxIndex As Integer
    Dim arrCtl() As String
    On Error GoTo EH
   
    ReDim arrCtl(Me.Controls.Count)
    intMaxIndex = 0
    
    ' Loop through all controls.  Order them in the array according to tab index
    ' Error handler below handles controls without a tab index property.
    For Each ctl In Me.Controls
        If ctl.TabIndex > intMaxIndex Then intMaxIndex = ctl.TabIndex
        ' Saves the control's name inthe array
        arrCtl(ctl.TabIndex) = ctl.Name
    Next

    ' Resize the array according to the highest Tab Index value
    ReDim Preserve arrCtl(intMaxIndex)
    
    ' Print out
    For I = 0 To UBound(arrCtl)
        Debug.Print "Tab Index " & I & ": " & arrCtl(I)
    Next
    Exit Sub
EH:
   ' This handles 'property not found' errors for controls that don't have a TabIndex property
    If Err.Number = 438 Then Resume Next
    MsgBox "ERROR " & Err.Number & ": " & Err.Description

End Sub

Open in new window


You'd have to apply your validation checks.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Why do you need to iterate/loop twice?

If you loop through all the controls can't you check each a control to see if it's one you are interested in.

If it is do your 'work'.
0
 

Assisted Solution

by:kentgorrell
kentgorrell earned 0 total points
Comment Utility
mbizip -

Your array does the job quite nicely. Everything is in order.

So to anyone who may make find this useful. Here is the code.

Those of you who are paid by the hour and enjoy writing the same code over and over will not appreciate this but for those of us who like fast dev -

The attached code will validate that the required data has been entered on your form just call it with Validate(Me) in the after update.

It does make the presumptions that the string "_ID" is in the name of foreign key fields and '_Date' is in the name of, you guessed it, date fields.That reminds me - I need to add somthing for numeric fields that aren't IDs but most of these default to 0.

So now all I do is enter 'Validate' in the tag of controls that need to be validated and enter something nice in the control tip text and call it.

If I don't want to raise messages, and set the focus to the recalcitrant control, then I just use the second optional parameter to supress messages and just return a boolean for if validation passed.

Now instead of writing a Validation procedure customised for each form, all I do is tag the controls and call the global function. Saves 30 - 60 minutes per form.

You could easily modify the procedure to return a string (separated by vbCRLF) of the controls that need attention instead of a boolean.
Public Function Validate(frm As Form, Optional blnRaiseAlerts As Boolean = True) As Boolean
' Generic Form Validation Function
' Tag controls to validate with "Validate"
On Error GoTo Error_Handler
Dim ctl As control
Dim strTitle As String, strDataType As String, strControlTip As String
Dim I, intMaxIndex As Integer
Dim arrCtl() As String

    strTitle = "Validation"
    Validate = True
    
    ReDim arrCtl(frm.Controls.Count)
    intMaxIndex = 0
    
    ' Loop through all controls. Add Controls with a Tab Order to the array according to tab index
    For Each ctl In frm.Controls
        With ctl
'            If .Tag = "Validate" Then
                If .TabIndex > intMaxIndex Then
                    intMaxIndex = ctl.TabIndex
                End If
                ' Saves the control's name inthe array
                arrCtl(ctl.TabIndex) = ctl.Name
'            End If
        End With
Next_Control:
    Next

    ' Resize the array according to the highest Tab Index value
    ReDim Preserve arrCtl(intMaxIndex)
    
    ' Print out
    For I = 0 To UBound(arrCtl)
        Set ctl = frm.Controls(arrCtl(I))
        With ctl
            If .Tag = "Validate" Then
                strControlTip = .ControlTipText & ""
                If strControlTip = "" Then
                    strControlTip = .ControlSource & ""
                    strControlTip = Replace(strControlTip, "_ID", "")
                    strControlTip = Replace(strControlTip, "_", " ")
                End If
                
                strDataType = "Text"
                If InStr(.Name, "_Date") Then strDataType = "Date"
                If InStr(.Name, "_ID") Then strDataType = "ID"
                
                Select Case strDataType
                    Case "ID"
                        If IsNull(ctl) Then
                            If blnRaiseAlerts Then
                                MsgBox "Please Enter the " & strControlTip, vbOKOnly, strTitle
                                .SetFocus
                            End If
                            Validate = False
                            GoTo Exit_Procedure
                        End If
                        
                    Case "Date"
                        If Not IsDate(ctl) Then
                            If blnRaiseAlerts Then
                                MsgBox "Please Enter the " & strControlTip, vbOKOnly, strTitle
                                .SetFocus
                            End If
                            Validate = False
                            GoTo Exit_Procedure
                        End If
                        
                    Case "Text"
                        If ctl & "" = "" Then
                            If blnRaiseAlerts Then
                                MsgBox "Please Enter the " & strControlTip, vbOKOnly, strTitle
                                .SetFocus
                            End If
                            Validate = False
                            GoTo Exit_Procedure
                        End If
                    
                End Select
            End If
        End With
    Next
    
Exit_Procedure:
   On Error Resume Next
   Exit Function
Error_Handler:
    Select Case Err.Number
        Case 438 ' No Tab Index
            Resume Next_Control
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Validate of Module Module2"
    End Select
    Resume Exit_Procedure
    Resume
End Function

Open in new window

0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Why do you need to order the controls?

Can't you just have one loop that goes through all the controls, checks their tag, does the validation etc?

By the way, perhaps I'm wrong but this only validates one control at a time?
0
 

Author Closing Comment

by:kentgorrell
Comment Utility
My solution show the complete code after employing mbizup's solution
0
 

Author Comment

by:kentgorrell
Comment Utility
imnorie -
The procedure is doing validation so the order is important. Very odd for the user if they are alerted to a control well down the form, they fix that, and then get taken to a control at the top on the next validation.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
I definitely can see the need for 'ordering' the validation messages.  It has a much more polished feel if the messages and required fixes appear in the order that the data was initially entered (ie: the tab order on the form).  We have had similar requirements in databases at my place of work.

The solution I have in place is more along the lines of my first comment at http:#a36487707, where the ordering and validation are done righ in the inner loop.  There are a lot of iterations involved with the nested loop structure, but there is no perceptible delay to the user.  I don't have nearly as many controls on my form as you do, though.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
It's a question of style I guess.
For complex data entry forms, I prefer to go with unbound controls and a command button to fire code that ensures that the pre-requisites are in place and then runs recordset code to actually commit the record to the tables.  The bonus is that you can build a form based on an un-updateable recordset and make it work.

The idea is sound, though.
Thanks for posting the finished code.

Nick67
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
What if the tab order isn't, well, in order?
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
@kentgorrell's solution is a good one, but it does require a bunch of up-front design work
<What if the tab order isn't, well, in order? >
Making SURE that it is in order is part of that--although it may not be absolutely vital for function.
After all, just because it is esthetically not nice to be knock on the head that you screwed up control 12, only to find out you had screwed up control 4 as well afterward it may still work anyway.
Another part of the up-front labor is that every control needing validating needs an AfterUpdate event to call the code.
Every control also needs ControlTipText.

Which is why I said
<It's a question of style I guess.>
I'm not sure the labor savings of a generic validator is worth the cost of the pre-reqs.

But then people say that about my unbound control /command button approach too :)
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
i would do it differently....
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<i would do it differently.... >
I suspect there are as many data-entry form styles as there Access developers :)
I know of folks who swear by pushing field validation rules down to the tables
Others do it in the controls
Others do it in the Form_BeforeUpdate event.
@kentgorrell is doing it in the AfterUpdate event of each control
I force the users to use my command button VBA code, and I do it just before a record gets committed to the tables by VBA code

Everybody justifies their approach based on what seems best to them--but I don't think that there is necessarily a 'right' approach
YMMV


0
 

Author Comment

by:kentgorrell
Comment Utility
oops - I meant that the code is called in the Before Update of the form.
It would be rather silly to use this code in the After Update of each control.
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
Pushing data validation down to table-level?

Isn't that kind of where it should be?

As far as I'm aware this isn't being done in the AfterUpdate event of each control, in fact I think it's to avoid that.
0
 
LVL 26

Expert Comment

by:Nick67
Comment Utility
<Isn't that kind of where it should be?>

That can be debated.
There are folks who pursue that approach (table-level) religiously.  I am not one of those and I am not in the majority
In my own situation, let's say with phone numbers, I could force a data validation rule down to the table
(XXX)XXX-XXXX is required
But now, some one needs to record an extension
Damn!
Is the validation at the table-level?
Is it at the control level?
Did I write some code to force a format on phone numbers--like adding the local area code if it is skipped?
Just where will it go BANG!  and in how many places?

Since I almost always have had grief with any BeforeUpadte event I have tried to craft, and they blow up with an unhappy Access error message , I don't pursue that course.  And esthetically I don't like records being created until the user is certain they want one.  A new record as soon as you start entering data in any control strikes me as pathological.  That an auto-number gets consumed when you bail (ESC) on entering data strikes me as inelegant.  Having a user la-de-dah around before committing to writing a record plays merry hell with the multi-user environment I run.  And since one of my very first forms was based on a multi-join query where referential integrity would bite the end-user who didn't plod along in order, I learned early on how to use unbound controls for the new data entry part of the form and continuous forms or subforms for displaying existing records.

Given that I was blowing in records using

dim rs as recordset
set rs = currentdb.openrecordset("somequery",dbopendynaset)
with rs
    .addnew
    !Somefield = me.SomeControl
    ...
    .update
end with


to add some code in ensuring that each of the controls had a good value--or if they needed to be coerced (names to proper case, or phone numbers to format) that it got done before the record got written wasn't a stretch.  And I know where that code is--because all the cmdAdd buttons on all of the forms perform that function.

But that's me.
Other people's mileage can and does vary
Some folks think my approach is too much work, or misusing Access.
I wouldn't do it any other way, though and like I said earlier
<It's a question of style I guess.>

:)
0
 

Author Comment

by:kentgorrell
Comment Utility
I do have cases where I use unbound forms and only commit the record when the user explicitly saves it. WIth a warning to save or abort if they try to close or move off the record.

This validation can still be used for those unbound forms.

Losing the autonumber is a little inellegant but, as the users should never see them, it shouldn't really matter. Any visible identifyer (eg a barcode) I always construct in code or let the user enter (and then ensure uniqueness in the control's before update) but where I can (but can't always) try to use an invisible Primary Key anyway. That way the user can change their visible code without upsetting the relatives.
0
 

Author Comment

by:kentgorrell
Comment Utility
One small adjustment to prevent controls in the footer replacing the controls with the same tab index as the ones in the detail section.
Line 17 should be -

For Each ctl in frm.Detail.Controls
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now