[AccessD] Column Positions

Hi Shamil

If this is in an Access environment, are there any benefits by specifying the Access Application object?
Normally I don't use it - I'm mostly in Access - but maybe I should?

Anyway, in Access this stripped down version seems to run with no glitches:

Public Sub ResetToQBEFieldsOrder(ByVal queryName As String)

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

/gustav


>>> shamil at users.mns.ru 30-03-2007 20:47 >>>
Hello Gustav,

Yes, that's OK - and now we start to play code optimization/generalization
game :)  (I'm using splitted code lines here to post ready to copy&paste
code, hopefully it will not be screwed anymore):

Public Sub ResetToQBEFieldsOrder( _
      ByVal queryName As String, _
      Optional ByRef rapp As _
       Access.Application = Nothing)
Dim app As Access.Application
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef
Dim frm As Access.Form
Dim ctl As Access.Control
    Set app = rapp
    If app Is Nothing Then _
       Set app = Access.Application
    With app
        Set dbs = .CurrentDb
        Set qdf = dbs.QueryDefs(queryName)
        .DoCmd.OpenQuery queryName, acViewNormal
        Set frm = .Screen.ActiveDatasheet.Form

        For Each fld In qdf.Fields
           frm.Controls(fld.Name).ColumnOrder = _
              fld.OrdinalPosition + 1
        Next fld

        .DoCmd.Close acQuery, queryName, acSaveYes
    End With
    Set qdf = Nothing
    Set dbs = Nothing
    Set app = Nothing
End Sub

--
Shamil
 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, March 30, 2007 10:16 PM
To: accessd at databaseadvisors.com 
Subject: Re: [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?