Access 2003/ VBA form filter problem

[garfieldsevilla]

I have created a form which I want to launch from VBA. I tested the form in the Access UI and was able to roll through the table contents, editing and adding as required.

I then added the following line to another form to call the form:

DoCmd.OpenForm "FAso", , , "[DS]='" & Me.DN & "'", , , Me.DN

The form correctly launched a few times but now it will not. Further, when I now open the form in the UI, it does not show any records until I select RECORDS/ REMOVE FILTER, even though there is no filter set in the property and FLTR is not shown in the status bar.

I checked the status in VBA ON FORM OPEN using:

Debug.Print "Me.Filter is ", Me.Filter >> “”
Debug.Print "Me.FilterOn is ", Me.FilterOn >> “false”

Is appears as though a filter is permanently set. I ran the compact and repair but no change.

Can anyone help?

[NeoPa]

When your code is put in side the code tags it becomes clear why the debug code would give invalid results.

I suspect your filtering is related to saving the form at some stage after a filter has been applied. See what happens if you remove the filter (Records / Remove Filter) then save the form (not the data). Will it work correctly afterwards.

To see what the Filter & FilterOn property values are, simply use :

Debug.Print Me.Filter, Me.FilterOn

Welcome to Bytes!

[garfieldsevilla]

Thank you- the form now works.

Launching the form with DoCmd.OpenForm "FAso", , , "[DS]='" & Me.DN & "'" results in the form’s filter field being set to the results of "[DS]='" & Me.DN & "'" while the form is active. If there is a problem with the VBA in the form and you correct and save the code, the filter field is set to whatever was in there when the error occurred.

The problem is that when you launch a form with the filter set in this way, FLTR does not show in Access. If you open the Form in design, change the property, save, and open again the filter property remains- it is impossible to change the filter field in the property! The only way to remove the filter is open in the UI, RECORDS/ REMOVE FILTER, and then save.

To avoid this problem during development and to ensure nothing strange might happen once the DB goes live, I am now passing the filter field as an argument using this code in ON OPEN:

Me.FilterOn = True
Me.FilterOn = False
Me.Filter = "DN='" & Me.OpenArgs & "'"
Me.FilterOn = True

Regards

[NeoPa]

A new question has been moved to its own thread - How do I Stop Users Saving Over a Form Design.