[AccessD] Make query design view look like data view

For all you search challenged people (like myself)...

Published by Shamil it seems.  And (hangs head in shame) I have no clue 
HOW it works.  If I had to guess I would say that this is a case of "the 
query has to be open for it to work", and the form control manipulation 
causes DAO to "reach down" into the guts of the querydef and fix things 
up.  Not something I would have thought of trying I must admit.

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

John W. Colby
www.ColbyConsulting.com


jwcolby wrote:
> I found it (I think).  Tried it and it shut down Access without warning. 
>   Hmm...
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> jwcolby wrote:
>> Look up thread "Column Positions" from 2007-04-01.
>>
>> mumble mumble how do I do that mumble mumble never use the archives 
>> mumble mumble apparently I need to mumble mumble
>>
>> John W. Colby
>> www.ColbyConsulting.com
>>
>>
>> Gustav Brock wrote:
>>> Hi John
>>>
>>> Look up thread "Column Positions" from 2007-04-01.
>>>
>>> It can't be done but Shamil posted an alternative.
>>>
>>> /gustav
>>>
>>>
>>>>>> jwcolby at colbyconsulting.com 08-05-2008 17:27 >>>
>>> Gustav,
>>>
>>> That is what I am looking for.  I thought someone had code already written.
>>>
>>> Thanks for the info though.  I will go write the code if no one pops up 
>>> with it.
>>>
>>> John W. Colby
>>> www.ColbyConsulting.com 
>>>
>>>
>>> Gustav Brock wrote:
>>>> Hi John
>>>>  
>>>> Loop through the fields of the query and - for each field - set the ordinal position of the field to the index of the field?
>>>>  
>>>> /gustav
>>>>  
>>>>
>>>>
>>>>>>> jwcolby at colbyconsulting.com 08-05-2008 17:01 >>>
>>>> When you view data in a query you can move the fields around, slide this 
>>>> field left, then this field right etc. If you SAVE the query after 
>>>> that, then the fields when viewing data are in a different order than 
>>>> the fields when viewing it in design. When it is a big query (lots of 
>>>> fields) it is very confusing trying to find a specific field in design 
>>>> view when it "should be" next to this field (as seen in the data view).
>>>>
>>>> John W. Colby
>>>> www.ColbyConsulting.com