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