Filter and sort vba not working in Runtime

[jamierbooth]

Hi All. I'm using access 2000 to develop and my staff are using Runtime to use the DB. I understand that the right click and menu-type filter and sort options are disabled/not available on Runtime.

I recently made some additions to a continuous form for a user to click on a label (ie Forename) which fires an on click event triggering the following code:-

Private Sub SrtForename_Click()
Me.Refresh
Me.OrderByOn = True
Me.OrderBy = "Client Forename ASC"
End Sub

which then should sort the results by Forename.

I also have similar labels to filter the results using the following code:-

Private Sub FiltActive_Click()
Me.Filter = "Status = 'Open - Active'"
Me.FilterOn = True
End Sub

These work fine on full MS access 2000, but on Runtime it asks me to enter the forms parameter value again. This continuous form is based on a query which filters the results by member of staff by having

Like [Forms]![frmSearch]![cboHA]

in the criteria box of the relevent field. (ie, looks up the value on a previous form to get the filtered results).

Is there something I can add to the code enable run-time to work with this code or re-announce the parameters?

Cheers. Jamie.

 

[BlueIshDan]

Me.OrderBy = "[Client Forename] ASC"

 

[jamierbooth]

Sorry, that doesn't fix it....

 

[BlueIshDan]

try a requery after

 

[jamierbooth]

Sorry, again not working. I tried just adding me.requery as the last line of each little segment of code ie:

Private Sub SrtDoB_Click()
 
    Me.Refresh
    Me.OrderByOn = True
    Me.OrderBy = "[DoB] ASC"
    Me.Requery
    
End Sub

and

Private Sub FiltNew_Click()
 
    Me.Filter = "[Status] = 'New Case'"
    Me.FilterOn = True
    Me.requery
        
End Sub

Maybe to help figure out whats wrong here....
When clicking one of the labels to fire the code, a window would come up saying "Enter Parameters, Forms!frmSearchF!cboHA" or similar. Which, whilst it doesn't do this in the full MS Office version, it seems in Runtime its forgetting the query parameters that populate this form.

 

[BlueIshDan]

honestly, I have never got filters to work.
I always just set the RowSource of the form to a query.

SELECT * from table_name WHERE [Status] = 'New Case'
Often, when creating a form with filters I have a combo box that displays the fields, and another that auto populates with the distinct values of the selected field. When selecting a new value in the second combo box I generate a new sql statement for the form's RowSource

Private Sub cboDistinctValues_Change()
      Me.RowSource = "SELECT * FROM table_name WHERE " & cboFieldName.Value & " = """ & cboDistinctValues.Value & """ ORDER BY " & cboFieldName.Value & " ASC;"

End Sub

Something like that