re-order a records sequence using up and down buttons

[efandango]

Funny you should mention weird behaviour; some of my previous version of this
database have thrown a fit, and are display a very long error message about a
problem occuring while MS Access was trying to communicate with the OLE
server or ActiveX control; when I OK that message box, I get an even longer
message about somethin to do with the afterupdate not being able to do its
thing...

I do save two versions before I do any major changes to the database, I keep
one as the ultimate fallback for worse case scenarios, which the above was.

Anyway, back to your new code. I pasted your two code sets into the
respective button's on click events, and the Up code appears to work just
fine, but the Down code does nothing, at all, zilch?...

just to confirm, I have the OrderSeq set to sort in the Recourd Source
Select Query; here it is verbatim:

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
ORDER BY tbl_Street_Joiner.OrderSeq;

 

[Marshall Barton]

Hard to imagine why one would work and other does nothing.
Are you sure the down button's OnClick property contains
[Event Procedure] and that the code builder button takes you
to the new procedure?

 

[efandango]

Marshall,

I opened up the database tonight, and though the Down button was a little
erratic, for example it stopped at the 5th record down, then jumped two
places; i suspect it wasjust dirty records; since then, the DOwn code has
worked very well, and I didn't change it from when I last posted to you, so I
can only figure it was perhaps bad legacy data, or it my be a deeper
underlying sympton in the data supply to the query. I will keep an eye on it
and see how things go; suffice it to say, that all appears to be well.

thanks for your help.

regards

Eric

Hard to imagine why one would work and other does nothing.
Are you sure the down button's OnClick property contains
[Event Procedure] and that the code builder button takes you
to the new procedure?
--
Marsh
MVP [MS Access]

I pasted your two code sets into the
respective button's on click events, and the Up code appears to work just
fine, but the Down code does nothing, at all, zilch?...

 

[efandango]

Marshall, My mistake regardin my last post about the code actually working; I
mistakingly called up the previous version with the old (working code); just
to confirm that I have sinced delted the button, created a new one; called it
a slightly different name, and pasted the Down code in, and it still refuses
to move any records down... very odd; and I don't know what to do. Here is
the code with the new button:

Private Sub SortOrderUpButton_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

regards

Eric

Hard to imagine why one would work and other does nothing.
Are you sure the down button's OnClick property contains
[Event Procedure] and that the code builder button takes you
to the new procedure?
--
Marsh
MVP [MS Access]

I pasted your two code sets into the
respective button's on click events, and the Up code appears to work just
fine, but the Down code does nothing, at all, zilch?...

 

[Marshall Barton]

Marshall, My mistake regardin my last post about the code actually working; I
mistakingly called up the previous version with the old (working code); just
to confirm that I have sinced delted the button, created a new one; called it
a slightly different name, and pasted the Down code in, and it still refuses
to move any records down... very odd; and I don't know what to do. Here is
the code with the new button:

Private Sub SortOrderUpButton_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

Does the name of the button, SortOrderUpButton, have
anything to do with it not moving a record down? Other than
that I don't see anything that would prevent it from
working.

Are you certain that the OnClick property is set to
[Event Procedure]
and that the code builder button [...] for the OnClick
property takes you that event procedure?

To avoid confusion, be sure to delete any existing records
in the form's table before starting any testing.

 

[efandango]

Marhsall,

This database behaves a little odd... now (after a day or so of non-use) it
all seems to work ok with regards to the Down button. However, unlike the
previous code, this version doesn't seem able to deal with the very first
record being able to swap with the one below it (visuall speaking). In other
words, the first record is always a zero, and the other records are a higher
sequence no, and cannot swap say, OrderSeq 1, down to OrderSeq 0; this issue
only occurs on the very first record. I tried putting this code into the
OrderSeq AfterUpdate event:


Private Sub OrderSeq_AfterUpdate()
If Me.OrderSeq = 0 Then
Me.OrderSeq = Me.OrderSeq + 1

End Sub

but it doesn't change it from a 0 to 1

any ideas?

Marshall, My mistake regardin my last post about the code actually working; I
mistakingly called up the previous version with the old (working code); just
to confirm that I have sinced delted the button, created a new one; called it
a slightly different name, and pasted the Down code in, and it still refuses
to move any records down... very odd; and I don't know what to do. Here is
the code with the new button:

Private Sub SortOrderUpButton_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

Does the name of the button, SortOrderUpButton, have
anything to do with it not moving a record down? Other than
that I don't see anything that would prevent it from
working.

Are you certain that the OnClick property is set to
[Event Procedure]
and that the code builder button [...] for the OnClick
property takes you that event procedure?

To avoid confusion, be sure to delete any existing records
in the form's table before starting any testing.

 

[efandango]

Marshall,

I've solved the '0' OrderSeq problem by putting the code tweak in the Combo
Box After Update event:

Private Sub StreetName_AfterUpdate()
If Me.OrderSeq = 0 Then
Me.OrderSeq = Me.OrderSeq + 1
Me.Requery
End If
Me.Dirty = False ' Save record
End Sub

Marshall, My mistake regardin my last post about the code actually working; I
mistakingly called up the previous version with the old (working code); just
to confirm that I have sinced delted the button, created a new one; called it
a slightly different name, and pasted the Down code in, and it still refuses
to move any records down... very odd; and I don't know what to do. Here is
the code with the new button:

Private Sub SortOrderUpButton_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

Does the name of the button, SortOrderUpButton, have
anything to do with it not moving a record down? Other than
that I don't see anything that would prevent it from
working.

Are you certain that the OnClick property is set to
[Event Procedure]
and that the code builder button [...] for the OnClick
property takes you that event procedure?

To avoid confusion, be sure to delete any existing records
in the form's table before starting any testing.

 

[efandango]

Marshall, I posted two responses to your last post, please ignore them, they
are not true and working.

Here is the latest state of things:

The records on the first page/set of records work fine, up or down. But when
I go to any subsequent 'pages' of records then the Down button fails to work.

to explain the above comments about pages. I have a master form which just
contains the Street list title and unique ID. This form is linked to the
subform which contains the Streets list which is the form that we have been
working on and is giving the problems.

Marshall, My mistake regardin my last post about the code actually working; I
mistakingly called up the previous version with the old (working code); just
to confirm that I have sinced delted the button, created a new one; called it
a slightly different name, and pasted the Down code in, and it still refuses
to move any records down... very odd; and I don't know what to do. Here is
the code with the new button:

Private Sub SortOrderUpButton_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

Does the name of the button, SortOrderUpButton, have
anything to do with it not moving a record down? Other than
that I don't see anything that would prevent it from
working.

Are you certain that the OnClick property is set to
[Event Procedure]
and that the code builder button [...] for the OnClick
property takes you that event procedure?

To avoid confusion, be sure to delete any existing records
in the form's table before starting any testing.

 

[Marshall Barton]

Marshall, I posted two responses to your last post, please ignore them, they
are not true and working.

Here is the latest state of things:

The records on the first page/set of records work fine, up or down. But when
I go to any subsequent 'pages' of records then the Down button fails to work.

to explain the above comments about pages. I have a master form which just
contains the Street list title and unique ID. This form is linked to the
subform which contains the Streets list which is the form that we have been
working on and is giving the problems.

I need more specific details abount this "page" thing. It
sounds like the addresses table has a foreign key (long
Integer??) to the street list table and you want the
sequence number to start over for each record in the street
list.

If that's the situation, then I was not out in left field
and we need to go back to my original DMax code in the
form's BeforeUpdate event procedure to include the foreign
key field that links to the addresses table. The code
would be a little different because of the main form
containing the addresses primary key value:

If Me.NewRecord Then
Me.OrderSeq = Nz(DMax("OrderSeq", "tbl_Street_Joiner", _
"Street_Name_Joins_ID=" & Parent.pkfield), 0) + 1
End If

Be sure to check my guess at the field names Use the
foreign key field in tbl_Street_Joiner, which should be the
same as the Link Child property. The name on the right is
the primary key field on the parent form, which is is the
same as the Link Master.