Why Does "Server Filter" Get Stuck? 3

[HenryE]

Hi All,

I have an ADP 2002 as the front end for a SQL Server 2000 database. Occasionally, a form will get stuck on a particular record and refuse to go to another record.

I've found that when this happens, it is because the Server Filter has been set to the record the form is stuck on. Deleting this (i.e. setting Server Filter to blank) removes the problem, but it always comes back a few days or weeks later, and always with a different record.

Anyone know why this is happening, and what can fix it?

Thanks.

Henry

 

[cjowsey]

It can easily happen if you have the forms Allow Design Changes property set to All Views or if you make changes to a form (or the code behind a form) while running your application and then saving the form on exit.

Sometimes (not frequently) it just happens for no apparent reason. Then I have to reload a master copy of the application for the user.

 

[HenryE]

Thank you very much! I think, hopefully, it's fixed.

Have a star!

Henry

 

[jahooper]

I encountered this problem a good bit in the past, and I found that it was only occurring on forms where I had a table as the Record Source (which was left over from when the app was MDB and wasn't good design anyway). When I changed the Record Source from a table to a stored procedure for those forms, the Server Filter value never got stuck again!

 

[HenryE]

jahooper,

Thanks for the email. If I may ask: Why is using a table as the Record Source bad design (by the way, that is exactly the situation I have)? I'm much more familiar with Access than SQL Server, so I'm probably making many SQL Server mistakes.

Thanks.

Henry

 

[AppDev76]

Hi,
I have also moved from Access mdb to adp.
I also think having a table data source is not a good idea specially if you have users in romote offices.
The main reason being that when you have a bound table to a form, each time the user opens the form the whole recordset is sent to the user over the network and this will be really bad if you have thousands of records, it will be very slow for the user and sucks up the network.
In addition to that the records will not be up to date unless the user does a refresh.

In my oponin the best way is to send the query to the server, let the server to the processing and then send back what is needed not all the records!

 

[HenryE]

Hi,

The reason I was using a table in the first place as the data source for the form was that it enabled me to use dropdowns in the form.

Is there a way to allow dropdowns in Stored Procedures? It's easy to do with Views, but I don't know if it is possible with Stored Procedures.

Thanks.

Henry

 

[karpach96]

I have the same problem. "Server filter" stucks. I read all solutions that are provided in this thread. Form's Allow Design property set to Design View Only. When Record source set to view, problem remains. I try to use Stored Procedure instead of View (Query), but link doesn't work at all. I use such sintax:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Input Trip Form"

stLinkCriteria = "[Trip ID]=" & Me![Trip ID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I am trying to solve this problem for a week already. I need more solutions, please help me somebody. I am tired to copy master copy.

 

[cmmrfrds]

Have you tried clearing the filter before exiting the Form.

Private Sub Form_Close()
Me.ServerFilter = ""
End Sub

 

[karpach96]

Thank you, I will try. May be somebody know why server filter get stuck ?

 

[ChakaTheApeMan]

I found some strange behavior and have found no documentation to explain it. Maybe will find this useful or someone could elaborate on these issues.

Refactoring, to use filters has yielded some questions. I have just converted an access ADP that used MS SQL 7.0 to use MS SQL server 2000. Server filters do not work with ADPs using MS SQl 7.0. My refactoring effort was to start using these server filters and filters, for efficiency reasons dealing with reuse of record sets instead of requiring the SQL server.

Strange issue #1

After calling something like

DoCmd.OpenForm stDocName, , , stLinkCriteria


The form opens with the data filtered correctly no problem. However, if I close the form and open it again with a different filter I see the data applied to the filter used on the form that a closed.

Somewhat strange issue #2

If the form is already open I see the old data displayed

To Handel both of these issues I use the code below. I see if the form is already open, if it is I just reassigned the filter and refresh and set focus to pop the form forward. Otherwise, I open the form and then reset the filter and refresh it or I will see old data. This seems strange that I have to set the filter after I open it. If I don’t issue #1 gets me.

Private Sub Details_Click()
On Error GoTo Err_Details_Click

Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "ID = " & Me.ID
stDocName = "ProjectDetails"
If CurrentProject.AllForms("stDocName").IsLoaded Then
Forms!ProjectDetails.ServerFilter = stLinkCriteria
Forms!ProjectDetails.Refresh
Forms!ProjectDetails.SetFocus
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!ProjectDetails.ServerFilter = stLinkCriteria
Forms!ProjectDetails.Refresh
Forms!ProjectDetails.SetFocus
End If

Exit_Details_Click:
Exit Sub

Err_Details_Click:
MsgBox Err.Description
Resume Exit_Details_Click

End Sub

 

[ChakaTheApeMan]

If you set client side filters including orderBys the server filter appears to be stuck.

For example if I was setting, on close event, me.filter = "". This empty sting seems harmless, however is breaks the server filter without an error. You can no longer change the server filter after this. So doCmd.openForm with a where clause kept opening the record that was opened the first time the report was used.

Remove any Filter or OrderBy commands properties, or groupings. Mixing server filters and client side filters won’t work out.

If the sort order of your query is an issue. Microsoft has a Knowles base article on using store procedures with input parameters that is a work around. This works well and fast. But, if you have multiple forms that you’re linking data to this solution won’t allow you to refer back to them dynamically. So it has limitations that would require you to use extra forms.

 

[karpach96]

Now, I got it. Thank you so much, you open my eyes on this problem. Maybe somebody can help me to find northwind.adp or documentation how to use stored procedures with MS Access ADP? And one more thing. After user enters information on the form in one field and goes to another at this moment form blinking (like refresh form). Why is it blinking (I don't have any refresh subroutines) ?