Solved Ordering when loop through all the controls on a form

[allanc]

Dim Fields As Control

For Each Fields In Me.Controls
    If Fields.ControlType = acComboBox Then
        FieldsOnthisForm = FieldsOnthisForm & "," & Fields.Name & "'" & Fields.Value & "', "
    End If
    Next Fields

MsgBox FieldsOnthisForm

Hello Friends,

I am trying to loop through all the controls on a form to capture their names and values to insert them on a table using SQL. When I executed the above codes, the order is messed up. The first control the code looped was in the middle of the form. I am wondering what controls the order? I tried tab order, didn't work.

Thank you!
Allan

 

[MajP]

It is the order they are entered into the form. You could probably grab the tab order and sort on that before insert.

 

[Deleted member 147267]

Since you can drag any of them anywhere any time what is the point of worrying about the order of controls in a collection if you're storing their names in a table? Even the table has no guarantee of record order.

 

[moke123]

I am wondering what controls the order?

If I had to guess I'd say its the order in which they were added to the form

 

[allanc]

It is the order they are entered into the form. You could probably grab the tab order and sort on that before insert.

Thank you, that sounds like a great plan! Let me try coding that in

 

[allanc]

Since you can drag any of them anywhere any time what is the point of worrying about the order of controls in a collection if you're storing their names in a table? Even the table has no guarantee of record order.

Thank you friend!

We have a form which displays the table that stores these values. Users may open the form and find the record they would like to retrieve so It would be nicer if these values are stored in order. I guess I only need to make sure the columns are in a desired order on that table to achieve that

I would like to try MajP's suggestion on sorting them based on tab order. This is a good learning experience as I have never tried sorting based on tab order. Also, I think it might help with future debugging if the values on SQL are in a nicer order?

Cheers!

 

[allanc]

If I had to guess I'd say its the order in which they were added to the form

Thank you! I hope they were based on something we could easily control like the tab order

 

[theDBguy]

Hi. I agree with Micron. Even if you store the values in a table in some order, there's no guarantee those values will actually be stored in that order. The only way to guarantee record order is by using a query with an ORDER BY clause.

 

[MajP]

We have a form which displays the table that stores these values. Users may open the form and find the record they would like to retrieve so It would be nicer if these values are stored in order. I guess I only need to make sure the columns are in a desired order on that table to achieve that

I still do not understand this. The table stores names of fields? Can you provide a bit more info or a screen shot?

 

[The_Doc_Man]

Why do I smell a somewhat less than perfectly normalized situation (when I hear "storing names of fields" and talk of USING that info...)

 

[Deleted member 147267]

Users may open the form and find the record they would like to retrieve so It would be nicer if these values are stored in order.

I cannot make sense of that either. Controls belong to what's called a collection. I'm also of the opinion that their ordinal position in this collection reflects when they were added to the form. That has absolutely no bearing on where they are on the form or how they are listed in a table. Where they are on the form is easily changed, and that has no bearing on the order of records in a table. Now, column order will not change unless you move them in a table, but again, this should have no bearing on storing data.

Something is seriously messed up here.

 

[jdraw]

AllanC,

Can you tell us the purpose of this? Is it just an exercise or is there some more meaningful goal?
Also curious why you call these fields in the forms Controls.

 

[allanc]

Hello Friends,

Sorry for the confusion created. The form is created for users to input values (or insurance quote information) and to return a premium. The entire purpose is to improve the functionality that stores all the values on a quote into a table which allows users to retrieve later. (i.e retrieving these values on a blank new form again). The tables would look something like this.

Reference Number	Driver Name	Driver Address
12345	Allan	123 ABC St.

When users retrieve, the code is basically getting these values(e.g Control.Name is [Driver Name], Control.Value is "Allan") from the table and putting that on the user entry form. Currently, we're writing one line for each control name and value which is quite annoying when the requirement is to add 10 new pieces of information being collected (e.g Driver Age, Driver Gender)

I want to write a loop to collect all the control names and values and then store them in a table, and using a similar loop to retrieve these values on the form. I wanted to check the ordering because 1) On the form users click something to retrieve, it would be nice if the reference number should show first. I figured I could get this sorted by how the table is displayed on the form. 2) If the order is messed up, the SQL query would look like this

INSERT INTO table_name (Discount1, DriverName, Discount3, ...)
VALUES ("MultiVehicle", "Allan", "Hybrid", ...);

which isn't a bigger deal since they will still get inserted correctly. But maybe I wonder if I do little work here, it might be easier for future debugging if they are in correct order? (Discount1, Discount2, Discount3 -something like this)

 

[allanc]

Sorry for the late reply guys. We had some really bad snowy weather here yesterday so I stayed home. Micron should have experienced the weather yesterday. I recall he's based in Ontario Canada too

AllanC,

Can you tell us the purpose of this? Is it just an exercise or is there some more meaningful goal?
Also curious why you call these fields in the forms Controls.

From my previous jobs, we always call the boxes on a form "fields" (like the username field you have to enter when you register). I feel like I should stop doing that

 

[jdraw]

???Still confused Allan. But I'm from Ontario and can understand "snow" alias"blustery"/"blizzard".
Can you describe the business generally? Are you working with unbound forms? As has been said throughout this thread, something doesn't sound right.
Perhaps you could show us your table design(s).

 

[Deleted member 147267]

Allanc, at the risk of seeming impolite, what you're describing seems bizarre. I agree that info about your tables would be a good thing as I wonder why you cannot just use bound form(s) instead of what you seem to be doing. Even if you had to use unbound form, the order of the controls is not important for the Insert statement. All that matters is that the order of the fields on one side of that statement matches the order of the values on the other side.

Weather wise, things must be worse in some areas of the province than others. Cold and at times windy here but not enough snow to keep anyone home. Power was out for a while last night so that was a bit concerning.

 

[The_Doc_Man]

it might be easier for future debugging if they are in correct order?

From a technical viewpoint, it should make no difference if you have correctly named the columns. That will be enough for SQL to figure how to do what it needs to do.

From an aesthetic viewpoint, that's up to you.

 

[allanc]

From a technical viewpoint, it should make no difference if you have correctly named the columns. That will be enough for SQL to figure how to do what it needs to do.

From an aesthetic viewpoint, that's up to you.

Thank you! I will mark this as resolved - I wish I could explain this a bit better without creating confusion.

Thanks again everyone for helping

 

[Galaxiom]

Depending on the index number of the controls on a form is like expecting a table to keep the records in the order they were added. The difference is there is no Order By on the controls collection so if it changes you have no options.

They won't stay in that order if you have to import the them to another form. I wouldn't even count on them to stay the same in a compact and repair. Microsoft does not document anything about the persistent of their indexes so it would be foolish to depend on it.

 

[The_Doc_Man]

There is also the issue that if a control is disabled, it is possible that you can't work with all parts of it when you select based on tab order. AND there is the issue that you might have controls that intentionally have NO tab order (because tab order can be blank). I did this a couple of times when the control in question wasn't bound to a required field.