Problem with filters

• Sherif Maamoun

  Ten Centuries

  Points: 1254

  More actions

  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

• bobsterboy

  SSCommitted

  Points: 1549

  More actions

  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

• bobsterboy

  SSCommitted

  Points: 1549

  More actions

  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.

   

   

• Mike Jensen-169059

  SSC Enthusiast

  Points: 113

  More actions

  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