MS Access (2010) VBA filter not working on one subform

0
votes

I have been struggling to identify the source of this problem for the past few days. My Internet searches for what's causing this issue have not been fruitful.

This is a program uses Access as the front end UI for user to enter client information into the database hosted on SQL Server.

The interaction should be as follows:

  • User click on client list in main form
  • A list of accounts is displayed in main form
  • User click on an account
  • List of products is displayed in a tabular subform (for update/add new product)
  • User select one of the products
  • Corresponding monthly product values are displayed on another subform (not under the products list subform)

When the main form is initially loaded, the monthly product values filter value is 0 by default. The filter should be updated dynamically when one of the products is selected. It was done under the Form_Current event for the products form.

What I can't figure out is: The filter seems to stuck at the default state. I used the Debug.Print statement to check the latest filter value for the monthly product values form and it shows the correct product ID for the filter. But it's not updated on the UI at all.

What's even weirder is: it works (i.e. monthly values are listed) sometime when I made changes to the vba code (that sometimes not related to filter) and save it when it's in Application mode e.g:

.filter = "prod_id = " & iProdID

to

.filter = "[prod_id] = " & iProdID

But then when I change it to Design mode and start making changes not related to this issue, it stopped working when it's in Application mode again. The behavior is so unpredictable that I am not sure how to proceed. I even remove the form and recreate it again and it's still behaving similarly.

I am using the same way to work on the filer in VBA for the other subforms and they are behaving correctly. Hope to get some feedback on what I overlooked!

Thanks! Kiat

1
ms-accessvba
Check and see if your applied something to the order by or filter property that shouldn't be there. Also try adding a form.refresh or form.requery after the filter is applied. – Scotch
There are various problems with filters and sql server see if stackoverflow.com/questions/14581749/… helps. Also Allen Browne has some notes here pcreview.co.uk/forums/… that may or may not be relevant. – Fionnuala
Hi Scotch, the only filter that's on the form on default is "prod_id = 0", that when in application mode, it changes to prod_id = <actual ID> based on the selection of the products list form. I tried both refresh and requery before without any success. There's one time when it's under application mode, I edited the vba code for the filter from prod_id to [prod_id] as shown in my example in the post, it worked and then it didn't once I saved it and went to design mode and back to application mode again. I tried the same trick on other part of the code but the result hasn't been consistent. – Kiat

1 Answers

0
votes

I got it to work now. The original method to assign filter for the product values was to assign the filter value directly to the Product Values form (Form_ProductVals) from the Products form's (Form_Products) Form_Current event. That was causing the inconsistency in the results.

Now I changed the code to assign the filter value through the main Clients form (that contains Products and ProductVals subforms). So the code was changed from:

Form_ProductVals.Form.Filter = "prod_id = " & <new ID>

to

Form_Clients.ProductVals.Form.Filter = "prod_id = " & <new ID>

That does the trick. I don't have an explanation of the difference though.