Loop through statement to reorder columns in a datasheet subform

I have a datasheet that must display populated columns on the left-most side of the form.  The set of populated columns changes depending on what filter is applied to the datasheet.  I've got code that examines the filtered data, and drops an ordered list of columns into my lookup table.  Now I need to execute code to reorder the columns.

This code correctly steps through the ordered list, and prints the ColumnOrder statements;  the text presently being printed by Debug.Print need to be executed.  Is there a function that will execute the string as a ColumnOrder command?

10   strSQL = "Select LookUpDe, LookUp from LookUpTb where LookUpCategory = 'AcctsRecblDisplay' order by LookUpDe"
20      Set rs = db.OpenRecordset(strSQL)
30         rs.MoveFirst
40         Do While rs.EOF = False                       'loops through records in rs

 'Reorder columns based on numbers
50   strFieldName = rs.Fields("LookupDe")
60   intFieldOrder = rs.Fields("LookUp")

Debug.Print "Forms![ARcollectionFrm]![ARCollectionDatasheet].Form." & strFieldName & ".ColumnOrder = " & intFieldOrder

70         rs.MoveNext
80         Loop

'recordset is closed:
90         rs.Close

Open in new window

Paul Cook-GilesSenior Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Check out my article on customizing datasheets, the entire focus is on saving and setting column orders.  I do it by userid, but you could modify my table that stores the orders by user to do it by filter.

The key to getting the columns in the order you want them in is to start with the one on the far right, and set it's columnOrder property to 0, then do the same for the next column, setting it's columnOrder property to 0 will make it first and shift the previous one to the right.
Paul Cook-GilesSenior Application DeveloperAuthor Commented:
Dale, thanks for your reply.  Very cool code in your article, and it does point at a method I can use if present design cannot  work.  I understand the way the ColumnOrder property works (although I started my ordering from the left and assign ordinal numbers to each column).

What I'm looking for is a way to execute the ColumnOrder statement that I'm creating by looping through the rows of my lookup table to plug in the column name and position.  Something like
Execute ("Forms![ARcollectionFrm]![ARCollectionDatasheet].Form." & strFieldName & ".ColumnOrder = " & intFieldOrder)

Open in new window

Docmd.RunStatement "Forms![ARcollectionFrm]![ARCollectionDatasheet].Form." & strFieldName & ".ColumnOrder = " & intFieldOrder

Open in new window

,  if such a command exists.
Dale FyeOwner, Developing Solutions LLCCommented:
In my experience, the ColumnOrder property does not work that way; trust me, I tried.

The only way I know to get the columns is the the order [Field1], [Field2], [Field3]
is to use

Forms![ARCollectionFrm]!ARCollectionDatasheet.Form.[Field3].ColumnOrder = 0
Forms![ARCollectionFrm]!ARCollectionDatasheet.Form.[Field2].ColumnOrder = 0
Forms![ARCollectionFrm]!ARCollectionDatasheet.Form.[Field1].ColumnOrder = 0

Open in new window

Paul Cook-GilesSenior Application DeveloperAuthor Commented:
This is the code generated by the debug.print command inside the loop, and it works exactly as expected.  
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.AmountDuePerCarrier.ColumnOrder = 1
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.AmountDuePerWB.ColumnOrder = 2
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.ARCollectionID.ColumnOrder = 3
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.CarrierID.ColumnOrder = 4
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.CarrierPolicyNum.ColumnOrder = 5
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.CoverageBeginDt.ColumnOrder = 6
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.PolicyNum.ColumnOrder = 7
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.SpreadsheetID.ColumnOrder = 8
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.AssignedToID.ColumnOrder = 9
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.BrokerID.ColumnOrder = 10
Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.CarrierPolicyNum_Old.ColumnOrder = 11

Open in new window

I just need a way to fire it inside the loop.  :)
Dale FyeOwner, Developing Solutions LLCCommented:
You don't need an Execute or anything like that, simply use syntax similar to:

Forms![ARcollectionFrm]![ARCollectionDatasheet].Form.Controls(strFieldName).ColumnOrder = intFieldOrder

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.