By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 2,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Access 2003/ VBA form filter problem

P: 37
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Debug.Print "Me.Filter is ", Me.Filter >> “”
  2. 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?
Feb 21 '10 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,494
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 :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print Me.Filter, Me.FilterOn
Welcome to Bytes!
Feb 21 '10 #2

P: 37
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

Feb 22 '10 #3

Expert Mod 15k+
P: 31,494
A new question has been moved to its own thread - How do I Stop Users Saving Over a Form Design.
Mar 10 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.