Form Control Looping Order

[mjmact]

I'm hoping that this is something simple that I am just overlooking...
I have some forms on which I need to loop through the controls. Everything was working fine until I needed to change one of the controls from a text box to a combo box. When I made this change it changed the order that my loop went through the controls. Because of some other things that I am doing, this has created a bit of an issue for me. It doesn't break the database, but it is causing me a headache.

As an example...

I have a form that has three fields - Grade, Type, Comment - in that order. Originally they were all text boxes and when I looped through them they went in that order which is what I needed. Then I changed "Type" to a combo box. When I did that they then looped through in the order of Grade, Comment, Type - NOT what I need.

I'm using a For Each loop on the controls in the form. When I noticed the problem I added a line that did Debug.Print for the control name on each iteration of the loop.
Originally it spit out:
Grade_Label
Grade
Type_Label
Type
Comment_Label
Comment

Now it gives me:
Grade_Label
Grade
Comment_Label
Comment
Type_Label
Type

If I change Comment to a combo and change it back, it goes back to what the first list was.


I can fix this by changing Comment to a combo box and then back to a text box, but that seems very clumsy and silly. I imagine that in my greater use for this loop I could probably code around it - but that would be a lot of extra work if there is a simple way to fix the root cause.

I've checked the Tab order, but that is all in the correct order. I couldn't find another property of the controls that seemed to govern this, but maybe I overlooked it or it isn't in the box listing all the properties and values.

Does anyone know a good solid way to fix this, or is it something that I'll just have to work around?

 

[Norie]

If it's a For Each the order that it's looped in is usually according to the index of each control.

The index is set when you add a control to form, so depends on when you added it.

Not sure what effect changing a control would have but adding/deleting controls can mix things up a bit.

Why do you need to loop through in a particular order?

One way you could do that is by using the names of a control, perhaps listed in an array.

 

[mjmact]

Thanks for the reply. That is what I was beginning to think that it was - with the index.
I assume that using the Right Click -> "Change To" probably effectively delete the control and replaces it with one of the new type and similar properties/names/etc. which could explain why it would change its order in looping through the indices.

Ultimately what I'm doing is I've got a datasheet subform and a listbox. They have the same fields and I am changing them on the fly (switching the datasheet to a different one and the list box to match, etc.). These are suppose to line up and (barring this problem) I can loop through the fields on the datasheet to set the listbox columns widths to match those of the datasheet.

I have an idea of what I can do to get around this issue, but I was hoping that there would be an easier solution. For the time being I'll probably go with the rather clunky method of tricking it into having the correct indices by changing their types in order (at least until I have time to code a better solution).

 

[Norie]

Have you considered not using a datasheet?

For example, it's not too hard to set up a continuous form that looks a lot like a datasheet but has all the advantages of being a form.

Or am I missing something here?

 

[mjmact]

Yeah. Originally I had done that - using a continuous as a stand in for a datasheet, as well as just using a "single line" of the form in the "filter" part mentioned below. However for my purposes, it made it easier to use the datasheet.

I've got my forms performing double duty effectively. I have a form that has two subforms. On one I have the detail listed out more like a "Single" form that the wizard would generate. The record displayed in this form is dependent on what is selected in the other subform.

In the other subform I have a listbox of the available records (same recordset as the first subform and this is what controls what is showing in that first subform) and then I have - within this subform - another subform.
This sub-subform uses the same base form/sourceobject that the first one used but with the control sources (etc) removed and displayed in datasheet mode. This is used as a kind of on the fly filter - type something in on the datasheet part and it filters the listbox - click the listbox and it shows all of the detail for the "Single" style subform. So I am able to get away with only one subform to maintain by allowing the one to perform double duty in Single and Datasheet modes.

It probably sounds a bit overly complicated, but of the various iterations of what I've been doing its been the easiest to do and doesn't require nearly as much attention/maintenance/code/forms and aside from this one issue I've been fairly pleased with how its been working.

 

[Norie]

Sorry you've lost me.

You seem to be using a listbox as a kind of subform displaying all the fields from multiple records.

Also, a continuous form isn't realy a 'stand-in' for a datasheet.

With a continuous form you have far more control than a form in datasheet view.

I've just reread the post and saw mention of sub-sub forms and it also appears everything has the same data source.

What kind of data are you dealing with?

 

[mjmact]

Yeah, I was afraid that I hadn't explained very well, so let me try with a picture...

Below is a mockup of my form.
So I've got my buttons for controls and two subforms:
Subform 1 - Data Detail - Green
Subform 2 - Filters/FilteredList - Purple/Red - One subform that has both pieces.

The Data Detail section is similar to the "Single" form that the wizard would generate. The source object we'll say is frmGrade. Just a form with Grade, Type, and Comment. (There are many of these that it could be, but the Grade one is the most simple.)

The Filters/Filtered List subform contains two pieces - Red/Filtered List is a Listbox. Filtered List has a query for its Row Source. The query allows me to give it parameters to filter down to what I want to show from tblGrade. The record source from Data Detail is tblGrade. So ultimately tblGrade is driving Filtered List and Data Detail, but Filtered List is - in a round about way - filtered. As I click on the different records in Filtered List it changes which record is selected in the Data Detail section.

The Purple/Filters is a subform within the larger Red/Purple combined subform (the sub-subform I mentioned) with the source object of frmGrade again, however this time it is set to display as a datasheet. This lets me use the same source form but make them look different. Ultimately in Filters List it is just a single line of a datasheet - everything unbound allowing me to enter data into the fields without changing anything. As I type into the filters section, it filters down the red - Filtered List - section.

My problem came from this....
This is setup as a sort of template. Depending on what data I need to work with, I change out the subforms for Data Detail and Filters and the Row Source of Filtered List. As I do this though, some of these are much more complicated with many more items. I have some code that sets the width of the red listbox columns to the width of the purple (filters subform) columns so that everything lines up.
However, when I change the control type, it apparently changes the index of the control and makes it come last as I loop through the columns in the purple getting widths. So that When I apply the widths to the Filtered List section, the widths are not in order.


As for continuous form/datasheet... I definitely know that continuous gives far more control than datasheet - thats why I used it originally. However, for what I was doing it became far more difficult to control.
What I meant was that in much earlier iterations of this, I had a form that was a continuous form made to look like a subform, but I also used the form header which I couldn't use in datasheet view. In the header I put all of the things I needed for the filtering. But like I said, for how things were working, doing it with the continuous form, etc. just became a lot of stuff to maintain (more stuff to break really) when I saw this as an easier way.

I hope I've explained it better, I know these things can be pretty hard to understand when you aren't discussing it directly with examples, etc.