Access 2007 Runtime and Filtering, Sorting

[Jay]

Hi,

This is an Access 2007 question.

I would like to deploy an application I am working on using the Access 2007
Runtime so my customers don't have to purchase a copy of Access. In the full
version of Access 2007, I can filter or sort a form or report simply by
right-clicking on a field. Also, I can use the "Sort & Filter" group on the
Ribbon. However, neither of those options are available in Access 2007
Runtime.

After looking through my "Access 2007 Inside Out" book and searching online,
it appears that to accomplish the same thing in Access Runtime, I would have
to build my own right-click popup menu and VBA code, or write XML code to
show the proper Ribbon group. Is that correct, or is there an easier way to
do this?

Jay

 

[Douglas J. Steele]

You're correct.

There's no easier way of doing it.

 

[Golfinray]

I have seen ribbons for 2007 already written on the internet. Google up some
sites and see what you can find.

 

[Jay]

Thanks, Douglas.

Then allow me to vent a little, aimed at Microsoft, not Douglas.

By what convoluted logic was it decided that this obvious, extremely useful,
built-in functionality that almost any database application would use would
not be easily available in Access Runtime? It is not in any way modifying
the design of the database application, which is what Access Runtime is
supposed to prevent, and the code to do it is clearly already available (to
Microsoft). At the very least, we ought to be able to use some VBA code to
do something like:

Application.Ribbons.Clear
Application.Ribbons.Add(RibbonGroups("SortAndFilter"))
Me.AllowSortFilterPopups = True

It's not that I think I can't (eventually) get the functionality I want. I
am a programmer, so I can probably figure out how to mimic the popup
functionality and learn enough XML to show a ribbon. The point is that it is
very, very developer unfriendly and a waste of valuable time to require us to
"reinvent the wheel" or handcode XML in the age of object oriented
programming.

Jay

 

[Jay]

Yes, I thought of that also. It is just annoying that Microsoft would
disable this end-user oriented functionality in Access Runtime without an
easy way to reenable it.

Jay

 

[Tony Toews [MVP]]

I would like to deploy an application I am working on using the Access 2007
Runtime so my customers don't have to purchase a copy of Access. In the full
version of Access 2007, I can filter or sort a form or report simply by
right-clicking on a field. Also, I can use the "Sort & Filter" group on the
Ribbon. However, neither of those options are available in Access 2007
Runtime.

I've always used my own fields at the top of the continuous form to do
filtering. I prefer this approach as the user can then see exactly
what has been filtered.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Albert D. Kallal]

Hi,

This is an Access 2007 question.

I would like to deploy an application I am working on using the Access
2007
Runtime so my customers don't have to purchase a copy of Access. In the
full
version of Access 2007, I can filter or sort a form or report simply by
right-clicking on a field. Also, I can use the "Sort & Filter" group on
the
Ribbon. However, neither of those options are available in Access 2007
Runtime.

The filter button, and search is still in the navigation area of the form
with runtime.

And, it takes only a few minutes to add a ribbon with the same built in
filter and sort options. Once you done this, then it is available for all
forms.

or write XML code to
show the proper Ribbon group. Is that correct, or is there an easier way
to
do this?

Yes you do, but you don't have to create the whole ribbon from scratch, you
can simply
***specify*** built in ribbon groups using xml. (you have to be willing to
do some work here).

Remember, you have to put on a developers hat here, and do *some*
development of your application that you plan to deploy to users. If you're
not
willing to do some of this development stuff, then simply have your users
purchase the full edition.

When I purchase a accounting package built with visual studio, I don't
expect to get the visual studio tools and all the options that visual studio
provides. I expect to get an accounting package created by a competent
developer.

Remember that user hasn't purchased MS access, they're not going to be
trained in MS access, they not going to have the documentation and help
files either. If you really think they need the full edition access and they
are willing to spend the time learning ms-access, then get them to purchase
the full edition of the product. And you'll find that if they have enough
training and experience, they probably won't need you anymore either!
Furthermore, if they have the full edition, then they can start doing some
development on your work also.

In a sense, they can purchase these features from Microsoft by purchasing
full version, or have that person pay you to put the features in. So the
choice is either to put the money in your pocket, or Microsoft's pocket,
it's really your choice at this point in time.

Only you can decide in your own personal case if it's better to provide a
few options, and have that client pay you for those options. Or, simply have
the client purchase the full version. In fact, maybe while we are at this,
have the client purchase the whole application that is already made out
there!

In many of my cases, I don't want some of these filter options to be
activated anyway, and furthermore I don't want the person to be overwhelmed
with the MS access interface, because they not purchased MS access, nor can
I expect them to be trained in ms-access.

Furthermore, you don't have to develop these options from scratch, you
simply have to build a bit of user interface to provide them to the user.
So, it not like you're writing your own filter code all of sudden here, it's
simply a case that you as a developer have to **specify** and decide what
options will be exposed to the user (that's called being a software
developer).

As I said, you have to put on
developers hat, and start thinking about your applications from an end
user's point of view, not from your own point of view.

What you need to do in this case is simply sit down, and figure out what
kind of user interface is appropriate for the type of user that you have.
In most cases, if you simplify things, users will find your application far
easer to use than the full version of access anyway.

If the user knows about all these options and is asking for them, then they
likely purchased access anyway, or will find it worthwhile to do so and
they'll be able to develop some their own things that they need anyway.

I do however think that MS access is missing a ribbon editor (hey, it was a
big new release, and the runtime is provided for free, not major $$ like in
the past). So, lets hope some type of editor comes along.

Making ribbons is not hard. Once you sat down and spent part of an afternoon
building a few ribbons, you'll find that those ribbons can be used over and
over again.

 

[Jay]

Albert,

The filter button, and search is still in the navigation area of the form
with runtime.

That would help for forms, but not for reports. However, I haven't been
able to enable those either. Can you tell me how?

Remember, you have to put on a developers hat here, and do *some*
development of your application that you plan to deploy to users.

If you read my second post, you'll note that I said:

"It's not that I think I can't (eventually) get the functionality I want. I
am a programmer, so I can probably figure out how to mimic the popup
functionality and learn enough XML to show a ribbon. The point is that it is
very, very developer unfriendly and a waste of valuable time to require us to
"reinvent the wheel" or handcode XML in the age of object oriented
programming."

And, it takes only a few minutes to add a ribbon with the same built in
filter and sort options. Once you done this, then it is available for all
forms.

But it would take a few seconds to do something like:
Application.Ribbon.Groups.Add(DefaultRibbons.SortAndFilter)

Yes you do, but you don't have to create the whole ribbon from scratch, you
can simply
***specify*** built in ribbon groups using xml. (you have to be willing to
do some work here).
If you're not willing to do some of this development stuff, then simply
have your users purchase the full edition.

I am willing to do some work, see quote above.

When I purchase a accounting package built with visual studio, I don't
expect to get the visual studio tools and all the options that visual studio
provides. I expect to get an accounting package created by a competent
developer.

But you expect that accounting package to check if your journal entries are
balanced before they are posted. That is a basic requirement for any
accounting package. Similarly, simple sorting and filtering is a basic
requirement for just about any database program. It seems that Microsoft
could have added a few methods or proerties to let the developer easily turn
the existing functionality on or off, based on their requirements.

Remember that user hasn't purchased MS access, they're not going to be
trained in MS access, they not going to have the documentation and help
files either.

I would have to write documentation for my application anyway. I'd have to
tell them how to use the popup menu to filter whether I had to create the
popup menu myself or simply set an Enable property to true.

If you really think they need the full edition access and they
are willing to spend the time learning ms-access, then get them to purchase
the full edition of the product. And you'll find that if they have enough
training and experience, they probably won't need you anymore either!
Furthermore, if they have the full edition, then they can start doing some
development on your work also.

You're going way beyond what my initial point was. I was expressing
frustration that Microsoft seems to be requiring the developer to recreate a
few basic features that they already have the code for.

In a sense, they can purchase these features from Microsoft by purchasing
full version, or have that person pay you to put the features in. So the
choice is either to put the money in your pocket, or Microsoft's pocket,
it's really your choice at this point in time.

Only you can decide in your own personal case if it's better to provide a
few options, and have that client pay you for those options. Or, simply have
the client purchase the full version. In fact, maybe while we are at this,
have the client purchase the whole application that is already made out
there!

In many of my cases, I don't want some of these filter options to be
activated anyway, and furthermore I don't want the person to be overwhelmed
with the MS access interface, because they not purchased MS access, nor can
I expect them to be trained in ms-access.

Exaclty. So in your case, you would leave the default settings and the user
wouldn't see the sort and filter menus or ribbons. We're not talking about
whether the user can change the type of join in a query. We're talking about
sorting and filtering, which is a common end-user requirement. It just seems
strange to me that Microsoft would not provide an easy on/off switch for
these common, end-user requirements that they clearly have already coded.

Furthermore, you don't have to develop these options from scratch, you
simply have to build a bit of user interface to provide them to the user.
So, it not like you're writing your own filter code all of sudden here, it's
simply a case that you as a developer have to **specify** and decide what
options will be exposed to the user (that's called being a software
developer).

Now you're just being condescending.

As I said, you have to put on
developers hat, and start thinking about your applications from an end
user's point of view, not from your own point of view.

I am thinking from both points of view. The end user wants simple sorting
and filtering. I want simple properties and methods, like most other things
in Access.

What you need to do in this case is simply sit down, and figure out what
kind of user interface is appropriate for the type of user that you have.
In most cases, if you simplify things, users will find your application far
easer to use than the full version of access anyway.

And, if Microsoft had done this, it seems to me it would have been pretty
obvious that their users (us developers) would want a simple way to allow
end-users to sort and filter.

If the user knows about all these options and is asking for them, then they
likely purchased access anyway, or will find it worthwhile to do so and
they'll be able to develop some their own things that they need anyway.

I do however think that MS access is missing a ribbon editor (hey, it was a
big new release, and the runtime is provided for free, not major $$ like in
the past). So, lets hope some type of editor comes along.

Now that is much closer to my original point than the rest of your post.

Making ribbons is not hard. Once you sat down and spent part of an afternoon
building a few ribbons, you'll find that those ribbons can be used over and
over again.

Exactly. Microsoft already has the standard ribbons, and it doesn't seem
like it would have taken them more than an afternoon to add a few methods and
properties to VBA to make them much more developer friendly.

Jay

 

[Albert D. Kallal]

Albert,


That would help for forms, but not for reports. However, I haven't been
able to enable those either. Can you tell me how?

Actually I just took a look at this and you know what, I don't know how! As
far as I can tell that functionality been disabled. And in fact I'm 100% on
your camp that this functionality **should** be available when viewing
reports. I can try and make some kind of excuse, or perhaps point out that
filtering reports is a brand new feature of a2007, however I think this is a
bit of oversight here. In fact the real answer is is that report view is not
allowed in runtime, but ONLY print preview is. This is just a limitation of
the runtime right now. I'm not quite sure why this design decision was made.

(by the way, I think interactive report filters is a terrific built in
feature, and something that MS access is needed for years - it is new to
access 07).

I will play both sides of the fences say that I do considered iteractive
filters somewhat of an advanced option for my users. I could make the case
that this is something that users of the full version only get. The reason
why I make this point is if you look at the posts here, in the last year or
so here, you not seen anyone asking for this feature in the runtime (I
believe you're the first to ask for this, and point this out here).

(in fact if you look at the top number one requests in this newsgroups these
days, you can almost bet for sure that's what the MS access team is working
on - so it's not gonna be this feature at this point in time )

It's also very possible that since we never had this feature before, we
always gave our users a good solid work around (what this means is that
you're actually up a bit more up to speed on using 07 then a lot of us users
here!).

As a general rule I still think we developers should provide some type of
filter form because it removes effort of the end user to "discover" how to
do that filtering.

here's a bit a screen shots showing those filter forms:

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

However, I am going to put the suggestion or shortcoming in the suggestion
box to the developers team, because I do agree with you, and I don't know
why the actual interactive report filter options are missing (by the way,
it's not the fact that they're missing in the menus, it's the fact that in
the runtime you can NOT enable them. It is possible that this is a conscious
decision on the developers team, but I haven't heard either way (so, lets
ask for it!!).

I'll be the first to admit that I did not realize that interactive filtering
was not available, but on the other hand as you can see you're the first
person asking for it here. I agree it should be at least available to us
devleopers like the options are for forms (at runtime, but you'll have to
make the effort to expose that functionality).

Now you're just being condescending.

It wasn't my intention to be condescending here, and I do apologize if
that's the way I came off. I should point out that the filter options are
available in forms if you provide buttions, or a ribbon. (my real point was
it's just a question of exposing that functionality to the end user, not the
fact that as we have to develop it ourselves).

And, if Microsoft had done this, it seems to me it would have been pretty
obvious that their users (us developers) would want a simple way to allow
end-users to sort and filter.

Like I said for the most part, you don't see a lot of posts and questions in
the newsgroups asking for these filter options. And, for the most part as a
developer you'll provide the user with some interface to accomplish this
goal. I guess the question is is how much functionality will you provide to
your application in terms of interactive filtering (and how much
functionality were going to get built into the runtime for us?) - Most of
the options for filtering can be done on a form, and that filter can then be
passed to a report...even in the rutnime).

What options and features they put into the products is all based on the
feedback that they get. For example was only about three versions ago that
we finally got a built in printers collecton that allowed us to change the
printer in code without resorting to some complex API code.

The same thing goes for the file browse dialog, now that's built in, but
most of us still continue to use the api code because we've had that code
sitting around in our library grab bag of tools for years.

I still tend to think it's a different kind of target user when you start
having users interactively filter the report (as opposed to building some
kind of user interface that allows the user to select the report and the
critera they want).

At a certain point the kind of functionality you're looking for means that
that end user should be provided with a full copy of MS access. I know a
number of good developers in this group that simply tell their clients to
always purchase the full version when using ms-access. However, I think this
is also about providing us developers with great tools, and in that light I
think the report filtering should be available in the runtime.