Fix aggregate column location in crosstab query

[GSan]

I'm afraid it is still not working so I must be doing something wrong elsewhere.
Below is the order that I am calling things:

Set LegalServiceDB = CurrentDb

'Get the current version of the query that is used by the crosstab query
Set queryDefinition = LegalServiceDB.QueryDefs("TotalFileCountByNameAndType_Crosstab")

'Set the new SQL string
queryDefinition.SQL = sqlQuery

queryDefinition.Close
LegalServiceDB.Close

Set queryDefinition = Nothing
Set LegalServiceDB = Nothing

Call FixCrosstabOrder

Me.TotalFileCount_SubForm.SourceObject = "Query.TotalFileCountByNameAndType_Crosstab"

I will create a new Access file and just create this query to see if I can see what I am doing wrong.

Thanks for all of your help.

 

[TexanInParis]

After you run that code, what does the output of the query look like when you open it?

 

[GSan]

The output is the same as before...
The 'totals' column is the second column in the form view and the sql datasheet view.
The order is as follows:
Column 1 - Name
Column 2 - File Totals
Column 3 - Excel
Column 4 - Word
Column 5 - PowerPoint
Column 6 - PDF
Column 7 - Email
Column 8 - Other

If I run the query and move the totals column to the end and save it. The totals column is displayed at the end of the list on the form. However, the user can deselect any of the file types. When the user unchecks the checkbox of one of the file types and then selects it, the file type goes to the end of the list. Once all are deselected and then selected again, the totals column is the second column and the rest of the file types are listed in the order above.

 

[TexanInParis]

GSan-

That's not my experience. Try going to the Immediate Window and running the FixCrosstabOrder code from there. I assume you fixed the query name and the field name in the code I posted. That should put "File Totals" at the end. The code basically renumbers the columns.

Another experiment-

What happens if you go to the Immediate window and type:

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(0).Name

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(0).Properties("ColumnOrder")

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(1).Name

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(1).Properties("ColumnOrder")

(Press Enter after each entry.)

 

[GSan]

I did replace the query name and field name of the FixCrosstabOrder method.
This is the code:

Public Sub FixCrosstabOrder()
Dim db As DAO.Database, qd As DAO.queryDef, fld As DAO.Field
Dim intI As Integer
' Point to this database
Set db = CurrentDb
' Get the querydef
Set qd = db.QueryDefs("TotalFileCountByNameAndType_Crosstab")
' Initialize the column order
intI = 1
' Loop through all the fields
For Each fld In qd.Fields
' If it's not the final total field
If fld.Name <> "File Totals" Then
' Set the column order
SetColumnOrder qd.Name, fld.Name, intI
' Increment
intI = intI + 1
End If
Next fld
' Now push the final total to the end
SetColumnOrder qd.Name, "File Totals", intI
' Clear objects
Set fld = Nothing
Set qd = Nothing
Set db = Nothing

End Sub

I have never used the Immediate Window before so I am sorry for the previous message that I deleted. I had a little trouble figuring out how to use it.

I made sure the query was closed when I ran this method
I entered: Call FixCrosstabOrder
then checked the datasheet. The File Totals column is still the 2nd column on the table.

I ran your commands above and got the following results:

?currentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(0).Name

Name

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(0).Properties("ColumnOrder")

1

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(1).Name

File Totals

?CurrentDb.QueryDefs("TotalFileCountByNameAndType_Crosstab").Fields(1).Properties("ColumnOrder")

8

The FixCrosstabOrder method sets the column order to what I would like the display order to be. But creating and setting this property does not affect the display of the crosstab query.

 

[GSan]

I got it to work!

I ran the crosstab query. The datasheet display had the 'File Totals' column in the 2nd column. So I moved it to the last column and saved it. (Which I had done before).

When I opened the View Data Form, The 'File Totals' column was in the last position, which I expected. But when I removed the other file types and then added them back, the 'File Totals' column would be displayed in the last position. (This never happened before.)

So your code works perfectly. I just had to save the query with the 'File Totals' column in the correct position. Now, it is always the last position.

Thanks so much for all of your work!