Move a Record Up/Down in a Continuous Form !

[Prakash]

Hi !!

In a Continuous Form with say 10 records, I'd like to have 1 button
with an "UP" Arrow & another button with a "DOWN" arrow. By pressing
either the Up or the Down Arrow, I'd like the user to be able to MOVE
the records up or down the order.

Something like in a WinAmp or MusicMatch Playlist.

A little sample code would be much appreciated.

Thanks & Bst Rgds,
Prakash.

[jv]

docmd.GoToRecor d,,acnext
docmd.GoToRecor d,,acprevious

[Mike Preston]

On 29 Jun 2005 06:22:56 -0700, "Prakash" <si****@omantel .net.om>
wrote:

Hi !!

In a Continuous Form with say 10 records, I'd like to have 1 button
with an "UP" Arrow & another button with a "DOWN" arrow. By pressing
either the Up or the Down Arrow, I'd like the user to be able to MOVE
the records up or down the order.
This is not trivial. Your continuous form has a recordsource and that
recordsource is probably ordered based on some Order By clause. There
is no way that Access can, by itself, modify the order of the records
it displays unless the underlying values that are used by the Order By
clause are changed. Hence, in general, it just can't be done.

But let's assume that you have a field in your table/query established
for this ordering. You will need to modify that field so that the
ordering values are changed and then requery the continuous form so
that the records are re-displayed.
Something like in a WinAmp or MusicMatch Playlist.

A little sample code would be much appreciated.

No sample code from me, but an idea or two. Assuming you have your
field available for ordering the display, you will need to establish
rules on how that field is built. One way would be to have it be a
text field with length of x + 1 bytes. Use the leftmost x bytes to
establish your order and leave the rightmost byte set to 5.

When re-establishing this field for a record you wish to move up, you
will replace its value with the value of the record immediately above
and change the rightmost byte to 4. If down, the record immediately
below and 6.

Then re-establish the temporary ordering (using whatever mechanism you
used to establish it in the first case, substituting the ordering
field for the original criteria), requery, and you are done.

Somebody else may come up with a routine that establishes the values
of the ordering field for you.

mike

[RobertLeeWhite]

To stop errors from jumping out at you if you're at the first or last
record, use this:

Up button:
If Me.CurrentRecor d > 1 Then
DoCmd.GoToRecor d , , acPrevious
End If

Down button:
If Me.CurrentRecor d <= Me.RecordsetClo ne.RecordCount Then
DoCmd.GoToRecor d , , acNext
End If

jv wrote:

docmd.GoToRecor d,,acnext
docmd.GoToRecor d,,acprevious

[Bas Cost Budde]

No, that doesn't move records, just the focus or pointer or cursor

jv wrote:

docmd.GoToRecor d,,acnext
docmd.GoToRecor d,,acprevious

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[Bas Cost Budde]

Have a sequence number field with your records. Let's call it seqno.

Have your records sequentially numbered.

Then, for the Down button's Click event: (air code, watch line wrap)

sub btnDown_Click()
dim nHold as long, nMax as long
nhold=me.seqno
nMax=dmax("seqn o","yourTable") +1'we'll park the first entry there
currentdb.execu te "update yourtable set seqno=" & nmax & " where
seqno=" & nhold
currentdb.execu te "update yourtable set seqno=" & nhold & " where
seqno=" & nhold+1
currentdb.execu te "update yourtable set seqno=" & nhold+1 & " where
seqno=" & nmax
end sub

Prakash wrote:

Hi !!

In a Continuous Form with say 10 records, I'd like to have 1 button
with an "UP" Arrow & another button with a "DOWN" arrow. By pressing
either the Up or the Down Arrow, I'd like the user to be able to MOVE
the records up or down the order.

Something like in a WinAmp or MusicMatch Playlist.

A little sample code would be much appreciated.

Thanks & Bst Rgds,
Prakash.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[Fred Zuckerman]

"Prakash" <si****@omantel .net.om> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Hi !!

In a Continuous Form with say 10 records, I'd like to have 1 button
with an "UP" Arrow & another button with a "DOWN" arrow. By pressing
either the Up or the Down Arrow, I'd like the user to be able to MOVE
the records up or down the order.

Something like in a WinAmp or MusicMatch Playlist.

A little sample code would be much appreciated.

Thanks & Bst Rgds,
Prakash.

First, you'll need to add an integer field to your recordset. I'll call it
[Position]. Then you need to populate that field by cycling thru the
recordset in the initial sort order, setting [Position] sequentially (ie: 1,
2, 3, 4, ...)
Now for the code (caution: aircode)

Assume that you have a button to move the selected record "up" one position:

Private Sub btnUp_Click()
If Me!Position <= 1 Then
MsgBox "This record cannot move up"
Exit Sub
End If
Dim NewPos As Integer
NewPos = Me!Position - 1
Me!Position = NewPos
DoCmd.GoToRecor d , , acPrevious
Me!Position = Me!Position + 1
Me.Requery
Me.Position.Set Focus
DoCmd.FindRecor d NewPos
End Sub

Good Luck,
Fred Zuckerman