How to open form with proper serverfilter?

[Andrew Backer]

I am curious if there is a way to fix the serverfilter property? If a
form gets saved with it set, it doesn't appear that you can set it in
code afterwards.

I would like to do this :
DoCmd.OpenForm "...",,,"Id=7"

And have it actually filter to id # 7 no matter what is set in the
field. Right now, if there is "id=5" in the property, the argument to
OpenForm doesn't actually overwrite the property.

What is the best way to work around this bug? I have fallen back on
putting a fake sql statement (where id = 0) in as the recorddsource,
and then opening the form and setting the sql from the openMyForm()
function. It causes unneeded sql to be issued, but I can't figure out
how to stop it.

How do y'all do this? I did notice (to my surprise) that the subform's
'open' property is called before the parent forms.

Thanks,
Andrew Backer

 

[Steve Jorgensen]

I am curious if there is a way to fix the serverfilter property? If a
form gets saved with it set, it doesn't appear that you can set it in
code afterwards.

I would like to do this :
DoCmd.OpenForm "...",,,"Id=7"

And have it actually filter to id # 7 no matter what is set in the
field. Right now, if there is "id=5" in the property, the argument to
OpenForm doesn't actually overwrite the property.

What is the best way to work around this bug? I have fallen back on
putting a fake sql statement (where id = 0) in as the recorddsource,
and then opening the form and setting the sql from the openMyForm()
function. It causes unneeded sql to be issued, but I can't figure out
how to stop it.

How do y'all do this? I did notice (to my surprise) that the subform's
'open' property is called before the parent forms.

Unfortunately, what you're doing is just about the best you can do. You can
eliminate the unnecessary query by giving the form a blank recordsource
expression by default, but that can wreak havoc with any code you have behind
the forms. I recommend doing exactly what you're doing unless the performance
penalty proves to be annoying, otherwise, do the same thing, but with a blank
default Recordsource for the form.

 

[Vadim Rapp]

AB> What is the best way to work around this bug?

easy: learn not to save the form with serverfilter.

Or if you do, remember to reopen it in design mode, erase serverfiler, and
resave.

There's another similar bug which is even worse. If the user applies a
filter (non-server) to the form, or sorts the records in the form using
toolbar button, Access is saving the form automatically with that
filter/sort, and also with the current serverfilter. After that, opening
docmd.openform <name>,,,criteria won't work because saved serverfilter takes
precedence. Worst of all, this is happening on user machines even in
compiled ade and with runtime access. The only solution I was able to come
up with is making the deployed ade file read-only. For the users that have
"full" Access rather than runtime, it results in a warning message when
opening the ade; they were told to live with that.


Vadim Rapp

 

[Andrew Backer]

True, i can learn not to, as I have learned many other things. As I am
sure you know, despite being dilligent and careful and infallible, I
will eventually mess up.

What I have done is come up with a solution, though not a prefect one.

It is possible to open forms in design mode & hidden. I am using this
to clear out the server filter property on all the forms that use it,
so this can't happen. If this helps anyone else, there is the
approximate code I am using :

Private Function ClearServerFitler(frm As String)

'-- if the form is opened (in any view) ---------
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
'-- if the form is NOT in design view, close it and open it in
design view
If Forms(strFormName).CurrentView <> conDesignView Then
DoCmd.Close acForm, frm, acSaveYes
DoCmd.OpenForm frm, acDesign, , , , acHidden
Else
End If

Forms(frm).ServerFilter = ""
DoCmd.Close acForm, frm, acSaveYes

End Function

Now I just call this function for each form that needs to be cleared (
from a master function) before I export to an ADE. Not prefect, but
actually I can just drop into the immediate window and run it, so it's
still faster than actually clearing, ESPECIALLY for subforms.

Thanks for all your help guys

- Andrew Backer

 

[Andrew Backer]

Forgot to include the constants (and rename that strFormName back to
'frm' ) :

Const conObjStateClosed = 0
Const conDesignView = 0

I got these from my IsLoaded( formName ) function, which I got help on,
probably form allen or candice. Thanks guys.

- Andrew

 

[Andrew Backer]

God dammit, here is the working copy and it doesn't need external
constants. I posted that in the middle of dev, and didn't test it
properly. Was in a hurry

Private Function ClearServerFilterByName(frm As String)

'-- if the form is opened (in any view) ---------
If SysCmd(acSysCmdGetObjectState, acForm, frm) <> 0 Then
DoCmd.Close acForm, frm, acSaveYes
End If

DoCmd.OpenForm frm, acDesign, , , , acHidden
Forms(frm).ServerFilter = ""
DoCmd.Close acForm, frm, acSaveYes

End Function

 

[Andrew Backer]

Steve, thanks a ton. I usually find a solution of some kind, but have
no way of knowing if there is something else superior that I missed. I
have found a semi-workaround for this now, but I still need to use this
method often enough.

- Andrew