Solved Form Properties "Order By" and "Filter By" retains parameters.

[raziel3]

Not sure I phrased the question properly.

I have a form that opens and goes to new record. Data Entry property is set to No.

Everything works great for the past couple of months but now I get a Runtime Error (see attachment).

Clicking on End opens up the form but I am unable to add records.

I've drill down the culprit to a parameter in the Order By property of the form. When I remove it, the form works as it should.

The question, and I have noticed this with the Filter By property also, why does closing off the database and/or the form retains the parameters in these properties?

 

[Uncle Gizmo]

It sounds like the issue you're experiencing is due to the form retaining old filter criteria, which can lead to unexpected results when the form is reopened. To prevent this, you can either clear the filter or turn it off when the form loads. This ensures that each time the form is opened, it starts fresh without any leftover criteria.

Add some VBA code to the `Form_Load` event.

Clear the Filter and Turn It Off
This method clears any existing filter and ensures it is turned off when the form loads:

Private Sub Form_Load()
    Me.Filter = ""  ' Clear any existing filter
    Me.FilterOn = False  ' Turn off the filter
End Sub

Or just turn Off the Filter:-

Private Sub Form_Load()
    Me.FilterOn = False  ' Turn off the filter
End Sub

 

[Uncle Gizmo]

The same idea can be applied to the order by property.

Private Sub Form_Load()
    ' Clear and turn off the filter
    Me.Filter = ""  ' Clear any existing filter
    Me.FilterOn = False  ' Turn off the filter

    ' Clear and turn off the Order By property
    Me.OrderBy = ""  ' Clear any existing Order By
    Me.OrderByOn = False  ' Turn off Order By
End Sub

 

[Eugene-LS]

the form retains the parameters in these properties

First set form properties FilterOnLoad and OrderByOnLoad to = False
Try code for OnClose Event:

Private Sub Form_Close() 'OnClose Event
    Me.Filter = ""
    Me.FilterOnLoad = False
    Me.OrderBy = ""
    Me.OrderByOnLoad = False
End Sub

 

[The_Doc_Man]

why does closing off the database and/or the form retains the parameters in these properties?

I'm going to offer an answer to that question (maybe). The form's "Filter" and "OrderBy" properties are form properties that would be saved if you altered the form and clicked the Save button (in the Access form design page) while those changes were in effect. OR if you had code in some action taken by the form that could be interpreted as saving the form, not just the data therein.

 

[Pat Hartman]

To continue Doc's explanation. If you don't want properties to be saved, then specify no save when you close the form. That argument is referring to the form's properties and has nothing to do with data. Of course if you are in the habit of making design changes and testing without first saving, this setting will come around and bite you. But, if you use best practices - ALWAYS save and compile prior to testing ANY change - you'll be fine.

Looking at the design view of the form, I don't know what the lookup field is referring to. Try removing that field. I would be surprised if this ever actually worked. If you want to sort by the customer name, add it to a hidden control on the form.

 

[raziel3]

The form's "Filter" and "OrderBy" properties are form properties that would be saved if you altered the form and clicked the Save button (in the Access form design page) while those changes were in effect

You are right @The_Doc_Man. This was the problem.