re-order a records sequence using up and down buttons

[efandango]

On a form, I want to move a record sequence order up or down one step at a
time, and refresh the overall new record list to display the new order using
just two buttons; Up and Down.

The idea is to be able to create a changeable street order list, allowing
for inserting addresses between an existing 'Run-Order' of addresses without
having to manually retype the whole list over again.

I need the process to be user friendly, hence the up-down buttons. I assumed
I would need some way of automatically generating the 'OrderSeq' number, but
don't know how this can be achieved without using an Autonumber field, but
the problem with that method is that I cannot then use that to re-order the
sequence. Can someone help me solve this problem.

The form has a combo box that allows me to choose/add a new address to the
list, this combo is linked to a master street list table.


My form table is:

Street_Name_Joins_ID Street OrderSeq
88 Main St 1
89 South St 2
90 East St 3
91 West St 4
92 Easy St 5

and for example, becomes...

Street_Name_Joins_ID Street OrderSeq
92 Easy St 1
88 Main St 2
89 South St 3
90 East St 4
91 West St 5

 

[Marshall Barton]

On a form, I want to move a record sequence order up or down one step at a
time, and refresh the overall new record list to display the new order using
just two buttons; Up and Down.

The idea is to be able to create a changeable street order list, allowing
for inserting addresses between an existing 'Run-Order' of addresses without
having to manually retype the whole list over again.

I need the process to be user friendly, hence the up-down buttons. I assumed
I would need some way of automatically generating the 'OrderSeq' number, but
don't know how this can be achieved without using an Autonumber field, but
the problem with that method is that I cannot then use that to re-order the
sequence. Can someone help me solve this problem.

The form has a combo box that allows me to choose/add a new address to the
list, this combo is linked to a master street list table.


My form table is:

Street_Name_Joins_ID Street OrderSeq
88 Main St 1
89 South St 2
90 East St 3
91 West St 4
92 Easy St 5

and for example, becomes...

Street_Name_Joins_ID Street OrderSeq
92 Easy St 1
88 Main St 2
89 South St 3
90 East St 4
91 West St 5

Your move up one row button can use code like:

Din db As DAO.Database
Dim lngKey As Long
Set db = CurrentDb()
lngKey = Me.keyfield
db.Execute "UPDATE table SET OrderSeq=OrderSeq+1 " _
"WHERE OrderSeq=" & Me.OrderSeq - 1
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1 " _
"WHERE keyfield=" & lngKey
Me.Requery
Me.Recordset.FindFirst "keyfield=" & lngKey
Set db = Nothing

The move down one row button's code would be the same with
the + and - reversed.

 

[efandango]

Marshall,

thanks for the reply, I will give that a try, but how do I generate the
OrderSeq number as I create each record, bearing in mind that I will want to
renumber(re-order) this figure, so Autoumber is not useful in this instance.

 

[Marshall Barton]

Once you have the OrderSeq field filled in for all existing
records in the table, then your data entry form can add new
records with the next OrderSeq value by using code like this
in the Form's BeforeUpdate event:

If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "table", _
"keyfield=" & Me.keyfield), 0) + 1
End If

 

[efandango]

the problem is that I don't have an orderseq value filled in, the user begins
with a blank continous form and adds new addresses via the combo box, which
becomes the order; it is when the user wants to insert a new address in the
middle of the list that I wanted the move up/down functionality. All I have
are: [Street_Name_Joins_ID] and [Street] fields, the [OrderSeq] field is
blank.

Once you have the OrderSeq field filled in for all existing
records in the table, then your data entry form can add new
records with the next OrderSeq value by using code like this
in the Form's BeforeUpdate event:

If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "table", _
"keyfield=" & Me.keyfield), 0) + 1
End If
--
Marsh
MVP [MS Access]

thanks for the reply, I will give that a try, but how do I generate the
OrderSeq number as I create each record, bearing in mind that I will want to
renumber(re-order) this figure, so Autoumber is not useful in this instance.

 

[Marshall Barton]

the problem is that I don't have an orderseq value filled in, the user begins
with a blank continous form and adds new addresses via the combo box, which
becomes the order; it is when the user wants to insert a new address in the
middle of the list that I wanted the move up/down functionality. All I have
are: [Street_Name_Joins_ID] and [Street] fields, the [OrderSeq] field is
blank.

When you say:
"the user begins with a blank continous
form and adds new addresses ..."
I take that to mean the user is adding new records, one at
a time, to the form's record source table. The code I
posted earlier will fill in the OrderSeq field as the
records are added.

The point I was trying to make is that the form's record
source table can not be prepopulated with records that do
not have the OrderSeq properly field filled in before adding
another record with a sequence number.

What mechanism do you plan on using for users to indicate
that they are done adding a new record so it can be saved?
If they are doing nothing beyond selecting an entry from the
combo box, then I suggest adding a line of code to the combo
box's AfterUpdate event:
Me.Dirty = False ' Save record

 

[efandango]

Marshall,

Yes, I do mean 'the user is adding new records, one at a time, to the form's
record source table', and I do now understand your point about the form's
source table not being pre-populated. Now that I have what seems a complete
method and process, I have tried your initial code and get the following
problems:

Compile error:

Method or data member not found

lngKey = Me.keyfield (with Keyfield highlighted)

and these two lines of code are highlighted in Red.

db.Execute "UPDATE table SET OrderSeq=OrderSeq+1" "WHERE OrderSeq=" &
Me.OrderSeq - 1
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1" "WHERE keyfield=" & lngKey

 

[efandango]

Marshall,

Thinking 'Keyfield' was literally the keyfield, in this case it wuld be
'Street_Name_Joins_ID', I made these changes:

Dim db As DAO.Database
Dim lngKey As Long
Set db = CurrentDb()
lngKey = Me.Street_Name_Joins_ID
db.Execute "UPDATE table SET OrderSeq=OrderSeq+1"
WHERE OrderSeq = " & Me.OrderSeq - 1"
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1"
WHERE Street_Name_Joins_ID = " & lngKey"
Me.Requery
Me.Recordset.FindFirst "Street_Name_Joins_ID=" & lngKey
Set db = Nothing

which removed the error message and by moving the WHERE statement down, it
made the Red highlight dissapear, but when I run it, i get:

Sub or Function not defined

and the 'OrderSeq' is highlighted in Blue in this line: WHERE OrderSeq = "
& Me.OrderSeq - 1"

I am, as you can see flying blind, and somewhat out of my depth...

 

[efandango]

I also get a similar 'Keyfield' related error on the form's BeforeUpdate
event: where the error message is: Method or data member not found, for this
code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "table", "keyfield=" & Me.keyfield),
0) + 1
End If

End Sub

where the 'Me.Keyfield' is error highlighted in Blue

 

[Marshall Barton]

I also get a similar 'Keyfield' related error on the form's BeforeUpdate
event: where the error message is: Method or data member not found, for this
code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "table", "keyfield=" & Me.keyfield),
0) + 1

You need to replace Keyfield here too.

 

[Marshall Barton]

Thinking 'Keyfield' was literally the keyfield, in this case it wuld be
'Street_Name_Joins_ID', I made these changes:
[snip]
db.Execute "UPDATE table SET OrderSeq=OrderSeq+1"
WHERE OrderSeq = " & Me.OrderSeq - 1"
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1"
WHERE Street_Name_Joins_ID = " & lngKey"

[snip]

You garbled the syntax when you retyped the statements that
I posted. It needs to be:

db.Execute "UPDATE table SET OrderSeq=OrderSeq+1 " & _
"WHERE OrderSeq = " & Me.OrderSeq - 1
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1 " & _
"WHERE Street_Name_Joins_ID = " & lngKey

If you get an error about Me.OrderSeq, the replace OrderSeq
with whatever name you are really using for the field in the
table.

Don't forget to replace "table" with the name of the form's
record source table (everywhere I used it).

 

[efandango]

Marshall,

I did that, and so far so good, but now I get this error in the UpButton code:


Sub or function not defined:


db.Execute "UPDATE table SET OrderSeq=OrderSeq+1"
WHERE OrderSeq = " & Me.OrderSeq - 1"

with the 'OrderSeq' in WHERE OrderSeq highlighted in Blue

i'm stumped...

 

[efandango]

This is the current situation: (it nearly works...)

If i pick record six with the Up button to move upto position 5, it instead
swaps places with position 1. this occurs regardless of which record i pick,
they all swap places with whatver position (record) 1 is at the time.

this is my current code for the UpButton:

Dim db As DAO.Database
Dim lngKey As Long
Set db = CurrentDb()
lngKey = Me.Street_Name_Joins_ID
db.Execute "UPDATE tbl_Street_Joiner SET OrderSeq=OrderSeq+1 " & _
"WHERE OrderSeq = " & Me.OrderSeq - 1
db.Execute "UPDATE tbl_Street_Joiner SET OrderSeq=OrderSeq-1 " & _
"WHERE Street_Name_Joins_ID = " & lngKey

Me.Requery
Me.Recordset.FindFirst "Street_Name_Joins_ID=" & lngKey
Set db = Nothing

THIS is my code for the form's before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "tbl_Street_Joiner",
"Street_Name_Joins_ID=" & Me.Street_Name_Joins_ID), 0) + 1
End If

End Sub

I also noticed that the OrderSeq field is showing just the number 1 for all
records, except the first record which shows a zero. I think this is central
to the problem, but can't figure out what is going wrong...

Thinking 'Keyfield' was literally the keyfield, in this case it wuld be
'Street_Name_Joins_ID', I made these changes:
[snip]
db.Execute "UPDATE table SET OrderSeq=OrderSeq+1"
WHERE OrderSeq = " & Me.OrderSeq - 1"
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1"
WHERE Street_Name_Joins_ID = " & lngKey"

[snip]

You garbled the syntax when you retyped the statements that
I posted. It needs to be:

db.Execute "UPDATE table SET OrderSeq=OrderSeq+1 " & _
"WHERE OrderSeq = " & Me.OrderSeq - 1
db.Execute "UPDATE table SET OrderSeq=OrderSeq-1 " & _
"WHERE Street_Name_Joins_ID = " & lngKey

If you get an error about Me.OrderSeq, the replace OrderSeq
with whatever name you are really using for the field in the
table.

Don't forget to replace "table" with the name of the form's
record source table (everywhere I used it).

 

[Marshall Barton]

This is the current situation: (it nearly works...)

If i pick record six with the Up button to move upto position 5, it instead
swaps places with position 1. this occurs regardless of which record i pick,
they all swap places with whatver position (record) 1 is at the time.

this is my current code for the UpButton:

Dim db As DAO.Database
Dim lngKey As Long
Set db = CurrentDb()
lngKey = Me.Street_Name_Joins_ID
db.Execute "UPDATE tbl_Street_Joiner SET OrderSeq=OrderSeq+1 " & _
"WHERE OrderSeq = " & Me.OrderSeq - 1
db.Execute "UPDATE tbl_Street_Joiner SET OrderSeq=OrderSeq-1 " & _
"WHERE Street_Name_Joins_ID = " & lngKey

Me.Requery
Me.Recordset.FindFirst "Street_Name_Joins_ID=" & lngKey
Set db = Nothing

THIS is my code for the form's before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "tbl_Street_Joiner",
"Street_Name_Joins_ID=" & Me.Street_Name_Joins_ID), 0) + 1
End If

End Sub

I also noticed that the OrderSeq field is showing just the number 1 for all
records, except the first record which shows a zero. I think this is central
to the problem, but can't figure out what is going wrong...

It's too soon to be moving records up or down. First we
have to add the records correctly.

Before you add the first record, the form should only show
the blank new record. When you add the first record, by
selecting an entry in the combo box, the OrderSeq field
should show 1

Are you sure that the table was empty before adding the
first record?

The fact that you are not seeing any error messages implies
that there are no type mismatches or other gross mistakes.
So I think that whatever is wrong is fairly subtle.

Maybe I am misunderstanding the values in the combo box row
source fields?? Please provide the row source field list
and a couple of sample records. Your original post could
also be interpreted as having the ID value and the street
address concatenated into a single field. But if that were
the case, the there should have been several errors. The
code assumes that the Street_Name_Joins_ID field contain
values like 88 or 89, etc.

 

[Marshall Barton]

This is the current situation: (it nearly works...)

If i pick record six with the Up button to move upto position 5, it instead
swaps places with position 1. this occurs regardless of which record i pick,
they all swap places with whatver position (record) 1 is at the time.

Keep working on getting a 1 in the first new record's
OrderSeq field. Make sure the form's record source table is
empty before opening the form. Then the only thing that
should get involved is the one line in the combo box's
AfterUpdate event and the few lines in the form's
BeforeUpdate event.

In the meantime, I have an idea for a much simpler way of
doing the move up/down that I want to test when I get a
chance later today.

 

[efandango]

Marshall,

I have managed to get 1 in the first new record's
OrderSeq field. The problem is that they all contain 1, which means they
only ever alternate between the first record and the one that I select for
moving (up or down). I was expecting them to gain a sequence number each time
a new address was added to the list, but they all remain on OrderSeq #1.

When I use the Up button on the last record (say Seq6), it counts down each
click until it is one away from the records above it which are all Seq1, then
it jumps to position 2 and changes all the records below it to Seq2

But when I use the down button on the last record, it increments by 1 each
time to infinity. So the functionality is kind of there, but the stumbling
block appears to be the inablity to increase each new record by 1. If that
were possible, I think it may work.


For the record, this is the form's Before Insert Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "tbl_Street_Joiner",
"Street_Name_Joins_ID=" & Me.Street_Name_Joins_ID), 0) + 1
End If
End Sub

and this is the Combo Box 'After Update' Code:

Private Sub StreetName_AfterUpdate()
Me.Dirty = False ' Save record
End Sub



This is my Form's Record Source:
SELECT tbl_Street_Joiner.Address, tbl_Street_Joiner.Joiner_Title_ID,
tbl_Street_Joiner.StreetNameID, tbl_Street_Joiner.OrderSeq,
tbl_Street_Joiner.Street_Name_Joins_ID FROM tbl_Street_Joiner;

and this is the table layout with records:

Street_Name_Joins_ID Address Joiner_Title_ID StreetNameID OrderSeq
117 Arlington Way, EC1 9 26610 1
118 Armadale Road, TW14 9 26613 1
119 Baalbac Road, N5 9 27003 1
120 Cab Road, SE1 9 29520 1
121 Eade Road, N4 9 34055 -2
122 Fabian Road, SW6 9 34942 1
123 Fabian Road, SW6 9 34942 -1

 

[Marshall Barton]

I have managed to get 1 in the first new record's
OrderSeq field. The problem is that they all contain 1, which means they
only ever alternate between the first record and the one that I select for
moving (up or down). I was expecting them to gain a sequence number each time
a new address was added to the list, but they all remain on OrderSeq #1.

When I use the Up button on the last record (say Seq6), it counts down each
click until it is one away from the records above it which are all Seq1, then
it jumps to position 2 and changes all the records below it to Seq2

But when I use the down button on the last record, it increments by 1 each
time to infinity. So the functionality is kind of there, but the stumbling
block appears to be the inablity to increase each new record by 1. If that
were possible, I think it may work.


For the record, this is the form's Before Insert Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "tbl_Street_Joiner",
"Street_Name_Joins_ID=" & Me.Street_Name_Joins_ID), 0) + 1
End If
End Sub

I think I was somewhere out in left field about what you
have in the table. We need to change the BeforeUpdate code
to be:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq","tbl_Street_Joiner",0)+1
End If
End Sub

Don't forget that tbl_Street_Joiner needs to start out with
no records in it.

It seems that I have messed over my Access installation so
it's going to take quite a while before I can work on the
"new and improved" move up/down code.

 

[efandango]

Marshall,

that code gives me Red Line error:

I noticed that among other things, you ommited 'Street_Name_Joins_ID' from
the code

should I put it back in?

 

[efandango]

Marshall,

This my friend, works perfectly!....


If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "tbl_Street_Joiner", OrderSeq) + 1)


Thank you soooooooo much for your help.

Sorry to hear about your access installation problems, hope you are up and
running soon. I would be really interested in your improved code; it's good
to learn from real code.

once again, a huge thanks.

 

[Marshall Barton]

Turns out is was only my monster test database, which had a
bad reference in a deleted report??? The bad ref was
causing Access to crash at all kinds of strange points.
Just importing everything to a new blank database got rid of
the deleted report. Too bad I wasted so much tme chasing
the weird symptoms all over the place.

Whatever

Here's the simplified(?) move up/dpwn code:

Private Sub cmdDown_Click()
Dim lngSeq As Long

With Me.Recordset
If Me.OrderSeq < .RecordCount Then
lngSeq = Me.OrderSeq + 1
Me.OrderSeq = lngSeq
.MoveNext
Me.OrderSeq = Me.OrderSeq - 1
Me.Requery
.FindFirst "OrderSeq =" & lngSeq
Else
Beep
End If
End With
End Sub

Private Sub cmdUp_Click()
Dim lngSeq As Long

With Me.Recordset
If Me.OrderSeq > 1 Then
lngSeq = Me.OrderSeq - 1
Me.OrderSeq = lngSeq
.MovePrevious
Me.OrderSeq = Me.OrderSeq + 1
Me.Requery
.FindFirst "OrderSeq =" & lngSeq
Else
Beep
End If
End With
End Sub

Don't forget that the form's record source query must sort
by the OrderSeq field.