Solved Reset Query Column Order to Match Design View Order (1 Viewer)

[Weekleyba]

In a query, if you rearrange a column(s) in Datasheet View, it appears you can no longer rearrange the columns in Design View and have the rearrangement show up in the Datasheet View. Arranging any column in the Datasheet View seems to turn off some type of link between the two.
How do you 'reset' this so it matches the Datasheet View matches the Design View?

 

[arnelgp]

just a thought, maybe re-assin the SQL of the querydef to itself?

 

[Weekleyba]

That's probably a great thought, but I have no idea how to accomplish it.
I was thinking maybe there was a setting in the Properties Sheet I was missing. Something quick and easy.
It seems strange that you can't easily get it back.

 

[arnelgp]

copy this function in a Module.
to reset your query:

Call fnResetQuery("the_query_name")

Public Function fnResetQuery(ByVal strQueryName As String)
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim sql As String
    Set db = CurrentDb
On Error GoTo err_handler
    sql = db.QueryDefs(strQueryName).sql
    DoCmd.DeleteObject acQuery, strQueryName
    db.QueryDefs.Refresh
    Application.RefreshDatabaseWindow
    Set qd = db.CreateQueryDef(strQueryName, sql)
    'db.QueryDefs.Append qd
exit_point:
    Set qd = Nothing
    db.QueryDefs.Refresh
    Application.RefreshDatabaseWindow
    Set db = Nothing
    Exit Function
err_handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume exit_point
End Function

 

[MarkK]

Datasheet column order appears to follow the tab order.

 

[arnelgp]

Datasheet column order appears to follow the tab order.

yes, i supposed in Form,

In a query, if you rearrange a column(s) in Datasheet View

but the Tab Order menu is not available in query?

you can of course manually drag the columns to its
original position.

 

[MajP]

Here is a slightly simpler version.

Public Sub ResetQueryColumns(qdf_Name As String)
  Dim fld As DAO.Field
  For Each fld In CurrentDb.QueryDefs(qdf_Name).Fields
    fld.Properties("ColumnOrder") = 0
  Next fld
End Sub