[AccessD] Column Positions

Hi Shamil

Bright idea! I hate when I have to say Yes to save the SQL of the query and I forgot to rearrange a column I have moved for some reason. Now I can just run this routine.

The function can be simplified a bit by just using the count of fields:

Public Sub ResetToQBEFieldsOrder(ByVal queryName As String)

Dim app As Access.Application
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim index As Integer
Dim frm As Access.Form
    
    Set app = Access.Application
    Set dbs = app.CurrentDb
    Set qdf = dbs.QueryDefs(queryName)
    app.DoCmd.OpenQuery queryName, acViewNormal
    Set frm = app.Screen.ActiveDatasheet.Form
    
    For index = 0 To qdf.Fields.Count - 1
       frm.Controls(index).ColumnOrder = index + 1
    Next
    
    app.DoCmd.Close acQuery, queryName, acSaveYes
    Set qdf = Nothing
    Set dbs = Nothing
    Set app = Nothing
End Sub

/gustav

>>> shamil at users.mns.ru 30-03-2007 19:17 >>>
Hi John,

Here is how column order can be "reset" to the column order in QBE grid:

Public Sub ResetToQBEFieldsOrder(ByVal queryName As String)
Dim app As Access.Application
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim index As Integer
Dim frm As Access.Form
Dim ctl As Access.Control
    Set app = Access.Application
    Set dbs = app.CurrentDb
    Set qdf = dbs.QueryDefs(queryName)
    app.DoCmd.OpenQuery queryName, acViewNormal
    Set frm = app.Screen.ActiveDatasheet.Form
    index = 1
    For Each fld In qdf.Fields
       Set ctl = frm.Controls(fld.Name)
       ctl.ColumnOrder = index
       index = index + 1
    Next fld
    app.DoCmd.Close acQuery, queryName, acSaveYes
    Set qdf = Nothing
    Set dbs = Nothing
    Set app = Nothing
End Sub

I have just "cooked" this code as a sample - it seems to work not bad but
please test it carefully if you plan to use it in production...

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of JWColby
Sent: Friday, March 30, 2007 8:27 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Column Positions

I have no idea what happens if you don't either.  I believe that it does NOT
save your changes.  However if you DO save the changes, then the positions
in design view do not match the positions in data view, and I have never
discovered a way to "reset" them to match.


John W. Colby
Colby Consulting
www.ColbyConsulting.com 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Friday, March 30, 2007 11:44 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Column Positions

You mean, if you don't save the datasheet view, the query retains the new
positions? I don't think I've ever paid any attention to this -- seems like
I'm always asked to save the query -- but really, never paid attention to
what happens if I don't. Or, am I someplace totally different that you guys?

Susan H. 

In a query's datasheet view you can easily drag the columns around.
 
Is there any way to reset them back to the way they are positioned in the
query grid?

The only way I have found to do this is to create a new query and just copy
the SQL statement to the new one. 
Is there a easier way, or maybe a menu option?