0

I have an Access form with different functions users can do. They are pulling lists of data, all from 1 table. Based on different filters or operations they want to do, I dynamically construct the SQL, use that to update the query defs of a query, and then set the subform object to that updated query. The problem is that the one I update the subform source object to the query, I need the columns that show to be in the order of the query.

This is the code I use, at different points of use on the form, depending on if they are interacting with a combo box, entering a filter into a textbox control on the main form, and I use similar code to set the default sql/query defs on load of the form. Why don't the columns always show in the same order as the sql that was updated to the query's definition?

Dim fsql As String

fsql = "SELECT table1.field1, table1.field2, table1.field3, table1.field4, table1.field5, table1.field6, table1.field7 " & _
            "FROM tblVFileImport "

If Nz(Me.cboFilterA, "") = "" Then
    fsql = fsql & "ORDER BY table1.field2, table1.field3 "
Else
    fsql = fsql & "WHERE table1.field6 = '" &Me.cboFilterA & "' "
    fsql = fsql & "ORDER BY table1.field4, table1.field5 "
    Me.cmdExtrabutton.Visible = True
End If
    
CurrentDb.QueryDefs("qrySubformSpecs").SQL = fsql
Forms!frmDoStuff.Form.frmDoStuff_SubResults.SourceObject = ""
Forms!frmDoStuff.Form.frmDoStuff_SubResults.SourceObject = "Query.qrySubformSpecs"

The columns get in order some times, but not consistently.

I also want to fit the columns a small as they can, fitting the contents of their respective data, if that's possible. The column fit is not as big of a deal, because the user can select the left corner and then double-click between the columns if they really need to. The order of the columns is definitely more important here.

If anyone knows how to accomplish this, I would really love the help. Thanks!

FWIW, the backend is SQL, so this is all coming from 1 big linked table. The performance is quick and not an issue, but the column order is important and needing to work right.

missscripty
  • 529
  • 2
  • 11
  • 30
  • Can't replicate the issue of column order. Setting column width requires a form as SourceObject. – June7 Jun 09 '21 at 06:59
  • 1
    Review [this](https://stackoverflow.com/a/52763115/7296893). Using a form to display the data fixes a lot of issues, and it contains code to autofit the column widths. I've got an adapted variant to load SQL instead of a recordset but adapting it is trivial. – Erik A Jun 09 '21 at 07:08
  • Thanks, June7. I was seeing the same issue with the width issue. Not sure what was going on with the updating of the column order to follow the updated query def. I adjusted what we are doing on the form, so that I can use the same layout the whole time. I think I'm ok now. I could do an actual subform, but I didn't want to have to manage the form object, the field controls and datasheet labels etc. Just not enough time. – missscripty Jun 09 '21 at 14:49

0 Answers0