Post reply

Problem with filters

  • January 27, 2006 at 10:56 am

    #67135

    I have an ADP connected to SQL 2000. The project has a lot of forms and in some of them the user is prompted to click a number on the form to get into the details.

    My syntax for that is:

    dim cid as long

    cid = me.folderid

    docmd.openform "fmtravelfolder",,,"[folderid] = " & cid

    This is working fine but the problem I am having is that sometimes the detail form is stuck with that folderid so even if the user clicks a different number it still comes out with the same sticky folderid until I have to go into form design and clear the server filter.

    I have tried exit sub, filter on and off and set filter = 0

    Any help will be much appreciated as I have a lot of forms based on that technique. What adds to the problem is that I can not predict when it does happen

  • January 27, 2006 at 3:50 pm

    #617590

    I googled this using serverfilter and this is the best answer I've seen.  I've run into this before and I just clicked the filter button twice and it "fixes" it, or I've used views, too.

    All 3 messages in topic - view as tree

    <script language=javascript1.2></script><!-- -->

    Andrew Fountain via AccessMonster.com

    		Apr 15 2005, 10:55 am   show options

    Newsgroups: microsoft.public.access.forms
    From: "Andrew Fountain via AccessMonster.com" <f...@nospam.AccessMonster.com> - Find messages by this author
    Date: Fri, 15 Apr 2005 15:55:28 GMT
    Local: Fri, Apr 15 2005 10:55 am
    Subject: ServerFilter problem finally solved!
    Reply to Author | Forward | Print | Individual Message | Show original | Report Abuse

    Many people have reported the problem of the serverfilter property getting

    stuck on a form if the form is saved. Next time the user opens the form, it

    accesses the original record, not the new one. I searched the net

    fruitlessly for a solution, and so had to come up with one myself. The

    solution I have found works very well, so I thought I would post it so

    others can use it.

    Solution:

    The ususal way of opening a form on a specific record from VBA code is

    something like:

        stDocName = "NameOfForm"

        stLinkCriteria = "RecordID=" & ID     'stLinkCriteria is a string

                                            'containing the WHERE filter

        DoCmd.OpenForm stDocName , , , stLinkCriteria

    replace the last line with:

        doOpenForm stDocName, stLinkCriteria  'call to function that checks

                                              'link criteria is in place

    Put the following sub procedure somewhere in a module. It can be used by

    all your code:

    Public Sub doOpenForm(stDocName As String, stLinkCriteria As String)

      DoCmd.OpenForm stDocName, , , stLinkCriteria

      If Forms(stDocName).ServerFilter <> stLinkCriteria Then

                                    'test if was opened with correct filter

        MsgBox "Form was accidentally saved by user." & vbCrLf & "Now

    recovering it..."

        DoCmd.RunCommand acCmdDesignView            'switch to design view

        Forms(stDocName).ServerFilter = ""        'clear server filter

        DoCmd.Save acForm, stDocName        'save form with no filter

        Forms(stDocName).ServerFilter = stLinkCriteria    'put correct value

    into filter

        DoCmd.RunCommand acCmdFormView        'switch back to form view

      End If

    End Sub

    The only other thing that needs to be done is to make sure the user done

    not accidentally re-save the newly cleaned form,

    so every time you close a form that was opened with doOpenForm, add the

    acSaveNo parameter:

        DoCmd.Close , , acSaveNo    'Make sure changes to form design are not saved

  • January 27, 2006 at 3:52 pm

    #617591

    By the way, the reason this occurs, according to some of the threads, is that Access tries to "help" you with this by saving the filter value when you open it in design mode.  that's why the previous poster has to make the change in design mode.  Weird.

     

     

  • January 31, 2006 at 12:05 am

    #617884

    Sherif,

    One of the things that jumps out at me is that the form is bound to a table or a query.  If you are bound, then in the "onclose" event of the form try this:

    With Me

        .FilterOn = False

        .Filter = Null

     End With

    This will turn off the filter for this instance only and set the filter to nothing

    You can bind the form for design then remove the recordsource from the form properties. Then in the "on open" event of the form use this code for late binding of the recordsource:

    dim strsql as string

    dim rs as adodb.recordset

    if not isnull(me.openargs) then

         strsql = "Select * "

         strsql = strsql & "from tblTravelFolder "

         strsql = strsql & "where folderid = " & me.openargs & " "

         me.recordsource = strsql

         doevents

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply