How to "remember" sort order by user

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

Hi Y'all,
I have a Form/Subform that shows a number of Unbound images on a subform.
The images are loaded in by code on a command button. The images are
external and named to the primary key number. They are currently loaded in
what ever order they are in the external folder when the album(folder) is
created initially.

The users may want to reorder the images on the form. I have provided a
copy/paste temp image field for them to do this. So I can accomplish the
sort on the form. But when the form is closed the sort order is gone and
reappears the same as before the user sorted because of the underlying query
sort when the form loads.

It seems that if I just remove the sort from the query and the form that all
should be well. But I don't trust that Access 2007 will just continually
bring them up in the resorted order as nothing will have changed in the query
or table. To further complicate this there is only 9 images per page but
there may be 2-3 pages of 9 each on the subform per main form entry. So I am
at a loss as to the best way to accomplish this.

Hence my request for suggestions.

Thank you in advance this has been an invaluable tool for so many other
barriers I have had to scale.

Thanks,
Robin
 
Hi Robin,

one way would be to store the form OrderBy property in either a database
property or a table -- then apply the saved setting when the form loads.
Does each user have their own front-end?

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Thank you for your suggestion but I believe that will still leave the same
order. The users are not trying to sort Ascending or Descending. Rather the
Images come in to the program and are named by what ever the primary key is
set to at the time.

100.jpg
101.jpg
102.jpg
etc.

and the form shows them in the same order.

They can manipulate the images to say:

102.jpg
100.jpg
101.jpg

So that the photos follow in the order they want them to be printed on
reports and such. But the order in the table is still 100, 101, 102. So
when the form is opened again or the report is printed it only goes asc or
desc, which are the only two choices I can choose from orderby or any other
sort property I could find.

After sleeping on it I'm thinking of adding another field to the table to
keep track of the new sort order to be updated when the image order is
updated. But I'm afraid that may affect the function of the many queries the
form is based on.

Thank you very much for your input.

Robin
 
Hi Robin,

yes, it sounds like you will need a field in the table. I often add
this field to tables:

Ordr, integer
(Order is a reserved word so I remove a letter)

if your users will want the ability to order things differently, the
solution will be a bit trickier because you will need to create a
related table (in each front-end) with the primary key and the Ordr for
that user -- then you will need to do an outer join with this table in
the form Recordsource and sort by the Ordr field

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Hi strive 4peace,

You're on to something that I hadn't even considered. Users wanting a
different sort per user rather than just not asc/desc. Luckily, the images
will be of auto's and they need to be the same for each user, front, side,
back, etc., so once they're in the correct order any user that needs to
access that record will be satisfied but I'll certainly play around a little
with the added functionality.

Great Idea, Thank you so much
Robin
 
Hi Robin,

you're welcome

Don't know how your data is structured, but, since you have several
shots of the the same auto from different views, I would recommend
something like this:

Autos
- AutoID, autonumber
- fields to describe information about the auto
- OrdrAuto, integer -- order for the auto to be displayed (if applicable)

AutoViews
- AutoViewID, autonumber
- AutoID, long integer -- FK to Autos
- ViewID, long integer -- FK to Views
- FilePathName, text -- file path and file name

Views
- ViewID, autonumber
- ViewName, text -- front, back, etc
- OrdrView, integer -- order for the view to be displayed

FK = Foreign Key

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
(: have an awesome day :)
*
 
Hi,

The structure was quite similar I just needed to add the additional table to
record the sort preferences, Views.

Works Like a Charm.

Thank you again, so much.
Robin
 
Back
Top