• I have upgraded from Access 2003 to 2013.  A Command Button on a form uses old VB Code:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70

    The code would essentially reload the form, re-running the form filter:

    (((NewSales.[SALE NUMBER]) In ([Enter Sale Number])))

    The form works directly off of a table, does not use a query.  

    The form is used to display Sale Data for a Sale,  fill in Survey Data for a specific Sale, and then filter to the next Sale Number.

    What code or other method can I use to re-open the form or re-run the filter on the form?

    Thank you for your help, Cameron

    Tuesday, July 23, 2013 4:12 PM

All replies

  • If all you want to do is clear the filter and then apply a new filter you can do it like this:

    Private Sub Command36_Click()
        Me.FilterOn = False
        Me.Filter = "NewSales.[SALE NUMBER] IN([Enter Sale Number])"
        Me.FilterOn = True
    End Sub

    Bill Mosca

    Tuesday, July 23, 2013 4:58 PM
  • The screen flashes but it always reverts to whatever the original Sale Number I entered when it first filtered upon opening the form.  At the bottom of the form screen in Access 2013 it has a button "Filtered" that I can click to un-filter the records which works, but when I click the button for the new click command it reverts back to the original Sale Number I entered.


    Tuesday, July 23, 2013 6:56 PM
  • Me.FilterOn = False should clear the existing filter which would bring you all records until you enter a number in the prompt. I tested it and it works. What is the code behind your button?

    Bill Mosca

    Tuesday, July 23, 2013 7:25 PM
  • It just looks like it is storing the first Sale Number I enter and keeps reverting back to it when I use the button.  Here is my code:

    Private Sub Command127_Click()

        Me.FilterOn = False
        Me.Filter = "NewSales.[SALE NUMBER] In ([Enter Sale Number])"
        Me.FilterOn = True

    End Sub

    Tuesday, July 23, 2013 7:49 PM
  • Also strange, when I switch to design mode then back to Form View it still reverts back to the Sale Number I originally entered when opening the form the first time instead of re-filtering and asking me for the Sale Number to filter by.  I have to close the form and re-open it to get the filter box to pop up.
    Tuesday, July 23, 2013 7:51 PM
  • With the form open in design mode, look at the form's filter property. I bet you accidentally saved the form while the filter had been set in the code.

    Remove the filter in the properties sheet, save the form and close it. That should clear the filter until you accidentally save it while the filter is applied again. {grin}

    Bill Mosca

    Tuesday, July 23, 2013 8:40 PM
  • I am not sure what is going on Bill, I even re-created a basic form with no filter in the properties sheet, created a button with the code, and once again it works the first time but not a second.

    I will have to play around with this again tomorrow.

    Thanks, Cameron

    Tuesday, July 23, 2013 8:50 PM
  • Cameron

    Please be sure to post back. I'm curious as to why this is happening. By any chance are you using a DoCmd.Close to close your form? One of the arguments is to "save/no save/ prompt to save" before closing.

    Bill Mosca

    Tuesday, July 23, 2013 10:00 PM
  • Bill,

    Thanks for the help.  I removed the on open filter, removed the filter on the form, changed several items in the properties box like changing filter on load to NO, and Allow Filters to NO.  But still every time I click the button for a second time it brings up the last data I entered.  It is storing my entry somewhere temporarily and will not allow me to rerun the filter.  The only thing I can do is unfilter the data on the form and type it in manually next to filter at the bottom of the form which searches through the records and essentially filters the data, however this takes a lot longer for it to filter.


    Thursday, July 25, 2013 8:36 PM
  • I can't think of anything else. I'd need to see the actual database at this point. If you'd like to let me have a look at it you can upload a zipped, sample db to the Files/2_AssistanceNeeded section at my Yahoo group,

    Bill Mosca

    Friday, July 26, 2013 2:10 PM
  • Bill Thanks for the help, I still have not found the issue, I even created a new form with just 2 fields to make the form simple and the command button.  Once again the command button runs the first time, but won't work thereafter.  When I click unfilter at the bottom of the form it does unfilter the records, but when I click the button it reverts back to the first number I typed into the enter sale number box.  

    I can guess that because this database was created in access 2003 and now I am opening it with access 2013, there could be something causing the problem.  I tried to run a repair but the repair tool (Compact & Repair) doesn't seem to work the same way in access 2003.  I did run the Compact & Repair tool but it did not seem to repair this issue.  

    If you come up with any other idea's please let me know and I will be happy to try them out.  As usual, if I find a solution to the problem I will post back so others can have a true solution to this problem.

    Thank you, Cameron

    Wednesday, July 31, 2013 3:41 PM
  • Cameron

    It could very well be a bug in 2013 using a 2003 db. I haven't heard of anyone else having this problem, but that doesn't mean anything.

    You might have to convert it to get the filtering to work. Or maybe wait for the next service pack to see if that fixes it.

    Bill Mosca

    Wednesday, July 31, 2013 4:59 PM
  • Bill, Is there any special way to convert from 2003 to 2013 or shall I say, is there a conversion tool?

    I went to "Save As" and selected .accdb converting it from the .mdb access version, it converted and I re-opened the .accdb file, tried the command button and same issue.  


    Wednesday, July 31, 2013 5:23 PM
  • Cameron

    It sounds like you converted it correctly. Try changing the filter string to this:

     Me.Filter = "NewSales.[SALE NUMBER] = [Enter Sale Number]"

    Bill Mosca

    Wednesday, July 31, 2013 6:57 PM
  • Bill, It ran the same.  

    I tell you what, in the morning I will create a new db replicating what I am trying to do and see if the new db has the same issue.  I will create it with Access 2013 as a .accdb file type.

    I will let you know the results.  If it works for you I don't see why it won't for me starting with a new db.

    Thanks, Cameron

    Wednesday, July 31, 2013 7:23 PM
  • I could only test it in Acc 2003 and 2010. I rarely upgrade during the first year a new suite is released even for home use.

    Hopefully, you get a new database to work. I'll be waiting to hear from you!

    Bill Mosca

    Wednesday, July 31, 2013 7:49 PM
  • It is possible that the (implicit) Parameter behaves differently in Access 2013. Try:

    Me.Filter = "[SALE NUMBER] = " & InputBox("Enter Sale Number:")

    There is no need to use the Table qualfier if the RecordSource of the Form is the Table [NewSales] which you stated in your original posted.

    P.S. I don't think you need to set FilterOn = False prior to the above statement and FilterOn = True after the above statement since the InputBox() function forces a new Filter String and if Access sees a new Filter String, it will automatically re-filter (if FilterOn is true).


    Van Dinh

    • Edited by Van Dinh Wednesday, July 31, 2013 11:27 PM Addendum
    Wednesday, July 31, 2013 11:19 PM
  • That didn't work for me.  It does not pull up the record.

    Thanks, Cameron

    Friday, August 02, 2013 3:49 PM
  • Well, this looks like a 2013 issue.  I built a 1 table, 2 field 1 form new 2013 db and the code does not work.  I am going to look up form creation tutorial to see how they recommend doing form searches in 2013.


    Friday, August 02, 2013 3:50 PM