Manually reordering

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a datasheet style subform containing process step records in order of
workflow. I need to enable the user to move a record up or down the list to
re-order the workflow.
(The type of functionality I need is available when using the table designer
by clicking and holding the selector bar then moving the entry up and down)
Anyone got any ideas?

Thanks
 
Raphman,
The order in which records display is controlled by sorting on some field
in the table.
You can't just physically move records up or down in a datasheet view...
as that would violate the sort order. You would need to change the value of
your sort field to a value that would place it in the proper position you
desire.

Here's a real life example... I created a Snowplow application where all
the customers were sorted by the order in which they were plowed out.
Cust PlowOrder
Smith 1.0
Brown 2.0
Davis 3.0
Another customer (Jones) requests plowing, and they would be serviced
between Smith and Brown. I make the at PlowOrder for Jones = 1.5, yielding
this new order...
Cust PlowOrder
Smith 1.0
Jones 1.5
Brown 2.0
Davis 3.0
--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."
 
Thanks Al for your swift response. Am I right in that you're saying that the
subform should contain an unbound control that is populated by the values I
abstract using a query so that the control is not dependent upon a table? If
so it's a good idea, but how does the user move the entries up or down. How
does Microsoft do it?
 
Thanks Al for your swift response. Am I right in that you're saying that the
subform should contain an unbound control that is populated by the values I
abstract using a query so that the control is not dependent upon a table? If
so it's a good idea, but how does the user move the entries up or down. How
does Microsoft do it?
Click to expand...

Reread Al's suggestion.

To sort by a manually entered field, the field MUST be in the table. A
calculated field in a query cannot be edited.

John W. Vinson [MVP]
 
Thanks John, I appreciate your reply as I do Al's. I must have been unclear
in the original question. I don't need to recalculate any values and the
field is not manually entered. I need to move a process stage from one place
in the list to another. A clunky way of doing this would to be to ask the
user where s/he would like the selected stage to go and then insert the stage
whilst rewriting all the other stages after it and then requerying the
dataset. I was trying to do something a little more elegant, but on
reflection I think this would involve dragging and dropping, which I don't
think is supported in VBA, or am I wrong?
 
Rapham,
We're getting there... with more information...
You wrote...
You're saying you have something like a [ProcessStage] field in your
table, and in what order those Process Stage records display in datasheet
view is what you are concerned with?
If so...
In this example (sorting by ProcessStage and Customer ascending)

Customer ProcessStage
Smith 1
Brown 2
Davis 3

Now... if you change the Davis record ProcessStage value changes to 1, and a
Refresh or Update occurs, the next time you look at the list it should
display as...

Customer ProcessStage
Davis 1
Smith 1
Brown 2
Davis has effectively "moved a process stage from one place in the list to
another."

You can't manually "force" a record to another position in the datasheet
view... you must change the value of it's sorting field/s that "sorts" that
value into the position you desire.

--
hth
Al Campagna
Access MVP 2007
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love, and you'll never work a day in your life."
 
Hi,

I have a datasheet style subform containing process step records in order of
workflow. I need to enable the user to move a record up or down the list to
re-order the workflow.
(The type of functionality I need is available when using the table designer
by clicking and holding the selector bar then moving the entry up and down)
Anyone got any ideas?

Thanks
Click to expand...

To re-order an item arbitrarily, create a numeric field with separate
numbers for each item and then create an index for it. Swapping
numbers will change the order when the table is viewed with that index.
 
Thanks OldPro, that's certainly a good work-around. However, the question
still remains: how do I literally move an item within a control like
Microsoft does in the table designer?
 
Back
Top