Remove Filter/Sort in VBA

[Guest]

Hallo!

Does anyone know the exact "VBA representation" of the forms context menu
entry "Remove Filter/Sort". (I need this as workaround for a problem that
makes recordsets disappear after a requery)
It is NOT "Requery + FilterOn = False" and also not "Requery + Filter = ""
Thanks all
Christoph

 

[Allen Browne]

It is possible to fudge referring to an item on a menu with:
DoCmd.DoMenuItem
where the arguments are numbers referring to the position of the items on
the menu (zero-based.)

But that style of programming is really bad, likely to break if the menus
are changed, or as new version are released. There has to be a better way of
solving the problem.

This kind of code should clear the form's properties:
Me.FilterOn = False
Me.Filter = vbNullString
Me.OrderByOn = False
Me.OrderBy = vbNullString

If that does not solve the problem, and this is a subform, you may find that
Access is assigning whatever it feels like to the LinkMasterFields and
LinkChildFields properties of the subform control. Clearing (or correctly
setting) those properties should address that issue.

The other possible cause is where the form's source query has parameters,
object references, or calculated fields that Access is misunderstanding. To
solve that, typecast the calculated fields, declare the parameters, or
replace the object references. More in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Thanks!

It is possible to fudge referring to an item on a menu with:
DoCmd.DoMenuItem
where the arguments are numbers referring to the position of the items on
the menu (zero-based.)

But that style of programming is really bad, likely to break if the menus
are changed, or as new version are released. There has to be a better way of
solving the problem.

This kind of code should clear the form's properties:
Me.FilterOn = False
Me.Filter = vbNullString
Me.OrderByOn = False
Me.OrderBy = vbNullString

If that does not solve the problem, and this is a subform, you may find that
Access is assigning whatever it feels like to the LinkMasterFields and
LinkChildFields properties of the subform control. Clearing (or correctly
setting) those properties should address that issue.

The other possible cause is where the form's source query has parameters,
object references, or calculated fields that Access is misunderstanding. To
solve that, typecast the calculated fields, declare the parameters, or
replace the object references. More in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message