Ordering the Controls Collection

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 }


       

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.

so the answer to your question
<Is there a way to order the Controls by Tab order?>  NO

see if this will apply to your case

https://www.experts-exchange.com/questions/22135313/Order-of-Controls-on-a-form.html

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.

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?

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

Select allOpen 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."

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.

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..

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

Jeff

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

Not sure why though.

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...

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

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.

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

kentgorrell,

read again my posts

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

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?

so, just to be clear, you are not interested regarding the way to do this as i posted in http:#a36488131

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
...

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'.

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?

My solution show the complete code after employing mbizup's solution

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.

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.

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

What if the tab order isn't, well, in order?

@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 :)

i would do it differently....

<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

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.

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.

<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.>

:)

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.

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