ServerFilter problem finally solved!

[Andrew Fountain via AccessMonster.com]

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

 

[cryptical via AccessMonster.com]

Does the subprocedure have to be public?

 

[Andrew Fountain via AccessMonster.com]

The reason that doOpenForm is public is so that it can be put into a module
and accessed from all the forms. You could have a private copy in each
form, but that would be inefficient.