Had a little error in my MS Access VBA when trying to use the Select method of an Excel Range. It turns out that you have to select the WorkSheet before the Range, otherwise you might get Error 1004

Sub RangeError()

    Dim xls As Excel.Application
    Dim xlsBook As Excel.Workbook
    Dim xlsSheet As Excel.Worksheet
    Dim xlsSheet2 As Excel.Worksheet

    Set xls = New Excel.Application
    Set xlsBook = xls.Workbooks.Open(PathToExcelFile)
    Set xlsSheet = xlsBook.Sheets("Sheet1")
    Set xlsSheet2 = xlsBook.Sheets("Sheet2")

    xlsSheet2.Select  'THIS LINE IS REQUIRED
    xlsSheet2.Range("C3").Select   'THIS IS THE PROBLEM LINE

End Sub

I recently needed to create a Modal Form that was a Continuous Form so that I could present the user with a selection of items. Initially, this was tall (full screen height) and very ugly so I needed to dynamically resize this form based on the number of Items that I needed to display. I also wanted to reposition the form so that it was in a place that looked right to the use. Keep in mind that I'm using Tabbed Forms and not Overlapping Forms style.

This is the simple code that I wrote! The qryTemp is just a table that I use for imported data. The GetUserName function just gets my username so I can just get my own data.

Private Sub Form_Load()
    ' 1 cm = 566.9291338583 twip, but using 580 for good measure!
    
    Dim intRows As Integer
    
    intRows = DCount("*", "qryTemp", "CreatedBy='" & GetUserName & "'")
    
    ' Add another row to prevent scrollbar from showing up
    intRows = intRows + 1
    Me.Form.InsideHeight = 2.6 * 580 + (340 * intRows)
    Me.Form.Move 580 * 10, 580 * 5
End Sub

Happy Coding, Coders!

I came across a request from a client who wanted the sorting from a Form to be transferred to a Report. Meaning that whatever was sorted for the Data Sheet view in a sub-form, to be transferred to the Report. This was a task that became almost impossible until I discovered a few things.

1. You cannot sort a Report Query and expect it to sort in a Report. It will not work.
2. You have to pass in the Forms Order By property, or update a global variable to store the sorting.
3. There is manual VB work involved.
4. There is also dummy Groups that are needed in the report that have to be updated when the Report is opened.

This is how I did it. I created a global variable to hold the Order By string from the Sub form. This variable was in a module, any module

Public g_strJobCostSort As String

I then wrote this code for opening the Report. Note that I had to remove certain sections of the Order String because of control names like Lookup_cboVendor, which is a dropdown in the sub-form.

    Dim strSQLSort As String
    
    If Nz(Me.subfrmJobCostItems.Form.OrderBy, "") <> "" Then
        strSQLSort = Replace(Replace(Me.subfrmJobCostItems.Form.OrderBy, "[qryJobCostItems].", ""), "[Lookup_cboVendor].[Company]", "[CompanyName]")
        g_strJobCostSort = strSQLSort
        DoEvents
    End If

    DoCmd.OpenReport "rptJobCostingCombined", acPreview, , "JobCostID=" & Me.txtJobCostID

I also created 2 buttons on the Sub-Form so the user could see the existing Sort Order and also clear it. One thing worth noting too is that the last column you sort on is actually the first column to be sorted on. It makes sense but you might need to try this is order for it to make any sense to you.

The Sorting button runs this code and the following dialog is shown.

MsgBox Replace(Replace(Me.subfrmJobCostItems.Form.OrderBy, "[qryJobCostItems].", ""), "[Lookup_cboVendor].[Company]", "[CompanyName]"), vbInformation, "Sorting"

The Clear Sorting code is this

    Me.subfrmJobCostItems.Form.OrderBy = ""
    Me.subfrmJobCostItems.Form.Requery

The Sub Report has the following Group On and Sort By properties/sections. I only added 4 Sort By sections but you can have as much as you like I guess. I noticed too that if I added a Group On but did not have a Group Header or Group Footer that it switched to a Sort by section. That's fine as it working the same way.

 This is the Code that I added to the Report_Open event in the subform. Note that I added " DESC" with a space at the beginning to make sure it was targeting the DESC sort order at the end, if there was one. If your columns have this combination, like Description, then you'll need to check that this code does not affect them. Basically this code gets all the sort columns, even if they're blank! This is why I chose to do On Error Resume Next. It's not the most glamorous way code, but it works and it will sort on the "up to" 4 sections that I added.

    Dim strSorting() As String
    strSorting = Split(g_strJobCostSort, ",")
    
    On Error Resume Next
    Me.GroupLevel(1).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(0)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(2).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(1)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(3).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(2)), " DESC", ""), "]", ""), " ", ""), "[", "")
    Me.GroupLevel(4).ControlSource = Replace(Replace(Replace(Replace(Trim(strSorting(3)), " DESC", ""), "]", ""), " ", ""), "[", "")
    DoEvents
    
    If InStr(strSorting(0), "DESC") > 0 Then
        Me.GroupLevel(1).SortOrder = True
    End If
    
    If InStr(strSorting(1), "DESC") > 0 Then
        Me.GroupLevel(2).SortOrder = True
    End If
    
    If InStr(strSorting(2), "DESC") > 0 Then
        Me.GroupLevel(3).SortOrder = True
    End If
    
    If InStr(strSorting(3), "DESC") > 0 Then
        Me.GroupLevel(4).SortOrder = True
    End If

Just as a courtesy, I have add the following links that helped me get to this solution after about 8 hours of trying. 

https://stackoverflow.com/questions/18836199/access-2010-vba-to-sort-a-report-with-existing-grouping/51014170#51014170?newreg=168265e41fd044cea63b1817e09e86ea

https://stackoverflow.com/questions/40402/what-is-the-command-to-truncate-a-sql-server-log-file

https://accessexperts.com/blog/2011/07/15/dynamically-sorting-forms-and-reports/

http://allenbrowne.com/ser-33.html - Allen Browne from Perth, Australia, is a legend!

Happy Coding, Coders!