Access Form data disappears on a VBA requery-CodePudding

I have a form with a continuous subform that allows users to conduct keyword searches. I want to add VBA code that will reset the form to all of rows as if the form was reopened.

My code is:

Private Sub ResetForm_Click()
    Dim SQL As String

    SQL = "SELECT  Data_tbl.Category, " _
        & "Data_tbl.Component, " _
        & "Data_tbl.Source, Data_tbl.Criticality " _
        & "FROM Data_tbl ORDER BY Data_tbl.Category;"
        
     Me.subCategoryList.Form.RecordSource = SQL
     Me.subCategoryList.Form.Requery
End Sub

The menu bar at the bottom of the form shows that there are 536 records. However the requery 'erases' what is displayed. All of the rows are devoid of data.

What is strange is that other subs work using the same setup with the SQL variable.

It must be something subtle but I just cannot figure it out.

Thanks in advance.

CodePudding user response：

Hy,

Is the button on your subform ? I would try to add the code bellow assuming that the button is on your subform.

Me.RecordSource = "Select * from Data_tbl order by Category Desc"

Me.Requery

CodePudding user response：

The problem appears to be with the "ORDER BY" clause. If I add DESC at the end, the requery works. In some cases, taking the default to ASC clears the for with no rows filled in. I don't understand why ascending won't work but at least I have a work around.