[AccessD] Column Positions

JWColby jwcolby at colbyconsulting.com
Fri Mar 30 12:57:49 CDT 2007


Shamil,

I straightened out the wrapping issue already.  All of your DIM statements
are getting pulled up to the first line for some reason.

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 Shamil
Salakhetdinov
Sent: Friday, March 30, 2007 1:50 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Column Positions

Yes, John, it works but it may need some more tuning/polishing.
Several code lines were improperly concatenated and wrapped - here is
(hopefully) properly formatted sample code version:

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

Enjoy!

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

Cool!  I will test it and if it works, throw it in my framework for the
future.

Thanks.

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 Shamil
Salakhetdinov
Sent: Friday, March 30, 2007 1:17 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Column Positions

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?


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list