Conflict between form sorting and form filtering

[steve7800]

Table ActivitiesList has five fields: ActID (AutoNumber), ActName, ActAuthor, ActDateCreated, ActDateClosed, ActItemID.

Continuous form ActivitiesList displays table ActivitiesList data. The form header has command buttons cmdActivitiesAll and cmdActivitiesOpen.

Command button cmdActivitiesAll has this VBA code to display all activities:

Private Sub cmdActivitiesAll_Click()
      Me.RecordSource = "SELECT ActivitiesList.ActID, ActivitiesList.ActName, " & _
         "ActivitiesList.ActAuthor, ActivitiesList.ActDateCreated, " & _
         "ActivitiesList.ActItemID, ActivitiesList.ActDateClosed " & _
         "FROM ActivitiesList " & _
         "ORDER BY ActivitiesList.ActDateCreated DESC;"
      Me.Refresh
End Sub

Command button cmdActivitiesOpen has this VBA code to display only open activities (activities whose ActDateClosed field is null):

Private Sub cmdActivitiesOpen_Click()
      Me.RecordSource = "SELECT ActivitiesList.ActID, ActivitiesList.ActName, " & _
         "ActivitiesList.ActAuthor, ActivitiesList.ActDateCreated, " & _
         "ActivitiesList.ActItemID, ActivitiesList.ActDateClosed " & _
         "FROM ActivitiesList " & _
         "WHERE (((ActivitiesList.ActDateClosed) Is Null)) " & _
         "ORDER BY ActivitiesList.ActDateCreated DESC;"
      Me.Refresh
End Sub

The form header has labels for each field -- lblActID, lblActName, lblActAuthor, etc. Clicking on a label will sort the form based on the selected field, either ascending or descending. Each label has VBA code similar to this code for lblActName:

Private Sub lblActName_Click()
    If OrderBy = "ActName" Then
        OrderBy = "ActName desc"
        Me.Refresh
    Else
        OrderBy = "ActName"
        Me.Refresh
    End If
End Sub

So, after all that, here is my problem: When the form opens, the label sorting works as it should. Clicking either the cmdActivitiesAll or cmdActivitiesOpen button disables the label sorting. Label sorting is only reenabled by closing and reopening the form and functions only until one of the aforementioned command buttons is clicked.

What can I do to be able to use the cmdActivitiesAll and cmdActivitiesOpen buttons while keeping the label sorting working?

Thanks.

Steve

[CJ_London]

when you say the code is disabled, are you saying it doesn't execute? or are you saying it executes, but doesn't do anything?

Only thing I see as a potential issue is your me.refresh lines, they should not be necessary.

some things to try

- remove the order by part from your recordsource and set the orderby per your label method
- alternatively set orderby to "" before you set your recordsource

[Micron]

When you alter the recordsource property, it's akin to requerying the form, so I agree that a refresh isn't necessary. Perhaps it's even part of the problem as you're doing a refresh (from the label click) which might conflict with the recordsource order by property you just set and then refreshed. When things don't work as expected, step through the code line by line to see what happens. It often reveals the issue.

Also, I'd use Me.OrderBy rather than assuming Access will figure out it's for the active form. I would also have one procedure with the common sql (that's most of it) and depending on which button is clicked, concatenate the criteria (or not) and the order by - or just leave the order by to the label clicks.

[steve7800]

Ajax and Micron,

I have altered the VBA code as you guys suggested, hope I got it right.

Private Sub lblActName_Click()
    If OrderBy = "ActName" Then
         Me.OrderBy = "ActName desc"
    Else
         Me.OrderBy = "ActName"
    End If
End Sub

Private Sub cmdActivitiesAll_Click()
      Me.OrderBy = ""
      Me.RecordSource = "SELECT ActivitiesList.ActID, ActivitiesList.ActName, " & _
         "ActivitiesList.ActAuthor, ActivitiesList.ActDateCreated, " & _
         "ActivitiesList.ActItemID, ActivitiesList.ActDateClosed " & _
         "FROM ActivitiesList; "
'         "ORDER BY ActivitiesList.ActDateCreated DESC;"
      Me.OrderBy = "ActDateCreated desc"
'      Me.Refresh
End Sub

Private Sub cmdActivitiesOpen_Click()
      Me.OrderBy = ""
      Me.RecordSource = "SELECT ActivitiesList.ActID, ActivitiesList.ActName, " & _
         "ActivitiesList.ActAuthor, ActivitiesList.ActDateCreated, " & _
         "ActivitiesList.ActItemID, ActivitiesList.ActDateClosed " & _
         "FROM ActivitiesList " & _
         "WHERE (((ActivitiesList.ActDateClosed) Is Null)); "
'         "ORDER BY ActivitiesList.ActDateCreated DESC;"
      Me.OrderBy = "ActDateCreated desc"
'      Me.Refresh
End Sub

Problem not resolved -- clicking on either of the two command buttons still disables the label sorting.

I placed a breakpoint at the IF statement in the lblActName_Click event. There is a difference in how the code is stepped through.

Here is how it goes when initially opening the form, before clicking either of the two command buttons, label sorting is working:

• Open form
• Click on lblActName
• Code stops at If OrderBy = "ActName" Then statement
• F8 code goes to Else
• F8 code goes to Me.OrderBy = “ActName”
• F8 the form sorts on ActName ascending, code jumps to Form_Current event
• F8 four times as the code steps through Form_Current
• F8 code jumps back to lblActName_Click sub End If statement
• F8 code goes to End Sub statement
• It’s over, the form has been properly sorted

Here is how it goes when things go wrong:

• Open form, verify label sorting works OK
• Click on cmdActivitiesOpen command button, form is filtered to display only open activities
• Click on lblActName
• Code stops at If OrderBy = "ActName" Then statement
• F8 code goes to Else
• F8 code goes to Me.OrderBy = “ActName”
• F8 code goes to End If statement, no sorting has occurred
• F8 code goes to End Sub statement
• It’s over, the form has not been sorted

Note that in the latter sequence, the jump to Form_Current does not occur. I don’t know what that means.

Here is the code in the Form_Current() event:

Private Sub Form_Current()
  
' 2019-0429 this is part of configuring a continuous form to highlight
' the selected record
  
   Debug.Print "********** " & Time; " frmActivitiesList -- enter " & _
      "Form_Current; CurrActID = " & CurrActID & "; ActID = " & ActID
   Me.CurrActID = Me.ActID
   Debug.Print "********** " & Time; " frmActivitiesList -- exit " & _
      "Form_Current; CurrActID = " & CurrActID
End Sub

Appreciate your help and any follow up advice.

Steve

[CJ_London]

do you have any code in the form open or load event or any code that may be run prior to opening the form which disables errors such as DoCmd.SetWarnings False?

What I am wondering is whether the OrderBy = "ActName" is generating an error that is being suppressed


Another thought is that by setting the recordsource the order property is turned off so something else to try is

Else
    Me.OrderBy = "ActName"
    me.OrderByOn=true
    End If

although if worked when ordering after resettting the recordsource it may be a red herring

Another test is to not verify if the sorting works when you open the form - just go straight to cmdActivitiesOpen - does the sorting work correctly there?

[Micron]

If OrderBy

This might be interpreted as a variable because you're still not using Me where it really counts. If that happens to be the problem, then I'll bet you don't have Option Explicit at the top of every module right there with the Option Compare statement (or at least not this module). Add that to this module and then compile and I'm sure you'll see what I mean. I would bet your money that it will error out on every instance of OrderBy - and maybe more.

That is an option in the vb editor (Always Require Variable Declaration). As someone once said, 'don't use it and you deserve all the grief you get'.
Should you decide to enable it, it will not propagate to all your modules. You would have to add it manually to existing ones, and that might raise more errors that you'd have to fix, but that would be a good thing.

[steve7800]

Ajax,

Your suggestion re “Me.OrderByOn = True” seems to have done the trick. Form sorting and filtering now play well together.

The code for the cmdActivitiesOpen_Click event now looks like this:

  Private Sub cmdActivitiesOpen_Click()
        Me.RecordSource = "SELECT ActivitiesList.ActID, ActivitiesList.ActName, " & _
           "ActivitiesList.ActAuthor, ActivitiesList.ActDateCreated, " & _
           "ActivitiesList.ActItemID, ActivitiesList.ActDateClosed " & _
           "FROM ActivitiesList " & _
           "WHERE (((ActivitiesList.ActDateClosed) Is Null)); "
        Me.OrderBy = "ActDateCreated desc"
        Me.OrderByOn = True
  End Sub

The code for the cmdActivitiesAll_Click event is the same except it does not have the WHERE part of the SQL statement.

The code for the lblActName_Click event now looks like this:

  Private Sub lblActName_Click()
      If Me.OrderBy = "ActName" Then
           Me.OrderBy = "ActName desc"
      Else
           Me.OrderBy = "ActName"
      End If
  End Sub

Thank you for helping me with this.

Steve

[steve7800]

Micron,

The use of “Me.OrderByOn = True” statements seems to have resolved the problem, see posting to ajax.

I see what you are saying in that “If OrderBy” would more appropriately be “If Me.OrderBy”. I have made that change.

I verified that this module contains Option Compare Database and Option Explicit statements at the beginning. I routinely use those statements.

Thank you for helping me with this.

Steve

[Micron]

Wondering why you open the form with one sort, then allow one of 2 other sorts but you cannot ever get the original sort back? Or am I wrong about that?
You might find this easier by using a combo with a value list that can actually provide the sort order values. You'd only need code for the combo (AfterUpdate event). I always try to minimize the number of controls needed to perform a task.

[steve7800]

Hi Micron,

I open the form sorted on ActDateCreated DESC and showing only open activities. The cmdActivitiesOpen command button provides the same sort.

I had not considered a combo box but will keep it in mind. It seems to me that clicking on one of the top-of-column labels is easier and faster than a combo box.

Thanks again for the help

[CJ_London]

other option for sorting is to right click on the control, which also gives access to filter options as well