Allowing User to Resequence a Route (not autonumber)

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

Guest

I am trying to setup a Dumpster Pickup program. The dumpsters are set to be
picked up on various days of the week. However, when a house is finished or
a new one is being built, the list will change. The user wants to change the
order of the route for that day (also to be able to incorporate temp pickups).

What I am trying to figure out is an easy way for them to reorder them so
that their route will print in the proper order to save time and fuel.

I start out with 120 pickups on a given day and usually have changes every
month as well as temp pickups. I want to have a form that pulls up the
addresses and the current order with a way for them to change the order. I
thought about the MS way of highlighting an address and then doing the up and
down arrows to move it but with 120 items or more, but that seems like a
tedious way to do it. My thought was to have them change the number and then
on after update, do a re-sequence (or maybe on a cmd button). However I
don't know if that can be done and I have not figured out the VBA code that
even comes close.

Any and all ideas are welcome.

TIA

Vanya
 
Hi Vanya

What you are aiming to do with the number field is perfectly sensible, but
there are several issues to solve to get this to work reliably and
efficiently.

Presumably you want to be able to type (say) 28 into a box near the end of
the continuous form that has your 120 or so records, and have that record
jump up to position 28. The previous 28 becomes 29, and so on down to
whatever number the record previously was, the rest stay the same.

You would do this in the AfterUpdate event of the *form*, but only if the
value of the SortOrder number changed. In Form_AfterUpdate, it is too late
to know if the value changed, so you need a form-level variable that you set
in Form_BeforeUpdate to indicate whether Sort.Order.OldValue is the same as
SortOrder.Value, and then in Form_AfterUpdate you can avoid the sort if it
was. To complicate this, there are cases where Access (particularly 97 and
earlier) report the wrong record in Form_AfterUpdate, so I suggest saving
the Value as well as the Old Value into form-level variables in
Form_BeforeUdpate.

Inserting a new record needs to be handled differently than moving an
existing one. Again, you can't know if it was a new record in
Form_AfterUpdate, so this is another module-level variable to set in
Form_BeforeUpdate.

The General declarations section of your form's module (top, with the Option
statements) will therefore contain:
Private mvarSortOrderOld As Variant 'Previous value of the SortOrder
field.
Private mvarSortOrderNew As Variant 'New value of the SortOrder
field.
Private mbWasNewRecord As Boolean 'Flag: this was a new insert.

Then in Form_BeforeUpdate:
mvarSortOrderOld = Me.SortOrder.OldValue
mvarSortOrderNew = Me.SortOrder.Value
mbWasNewRecord = Me.NewRecord

In Form_AfterUpdate, call the attached function like this:
Call ReorderRecords(Me, "Table1", "SortOrder", "MyId", _
"MyId = " & Nz(Me.MyId, 0), mvarSortOrderOld, mvarSortOrderNew, _
Nz(Me.MyId, 0), mbWasNewRecord)

(Note that this does trigger another bug in Access. If this form has a
subform, and you reorder its records in Form_AfterUpdate and find your
record again, and the thing that triggers the save is tabbing into the
subform, then the Enter event of the subform does not fire.)

I'm breaking the newsgroup rules by attached the code, but it has many long
lines that would become unintelligible if included.
 
Ivan Grozney said:
I am trying to setup a Dumpster Pickup program. The dumpsters are set to be
picked up on various days of the week. However, when a house is finished or
a new one is being built, the list will change. The user wants to change the
order of the route for that day (also to be able to incorporate temp pickups).

What I am trying to figure out is an easy way for them to reorder them so
that their route will print in the proper order to save time and fuel.

I start out with 120 pickups on a given day and usually have changes every
month as well as temp pickups. I want to have a form that pulls up the
addresses and the current order with a way for them to change the order. I
thought about the MS way of highlighting an address and then doing the up and
down arrows to move it but with 120 items or more, but that seems like a
tedious way to do it. My thought was to have them change the number and then
on after update, do a re-sequence (or maybe on a cmd button). However I
don't know if that can be done and I have not figured out the VBA code that
even comes close.
Click to expand...


If the ordering field is a sequence of integers, then you
can use an UPDATE query to renumber the recods between the
old and new positions. Here's a procedure that seems to do
what I think you want:

Private Sub txtID_AfterUpdate()
Dim varOld As Variant, lngNew As Long
Dim strSQL As String

If IsNull(Me.txtID) Then Exit Sub
varOld = Me.txtID.OldValue
lngNew = Me.txtID
If varOld = lngNew Then Exit Sub
Me.txtID = 0
Me.Dirty = False 'save record with dummy number

If IsNull(varOld) Then
strSQL = "UPDATE ZZJunk SET ID = ID +1" & _
" WHERE ID >=" & lngNew
ElseIf varOld < lngNew Then
strSQL = "UPDATE ZZJunk SET ID = ID - 1 " & _
"WHERE ID Between " & varOld & " AND " & lngNew
ElseIf varOld > lngNew Then
strSQL = "UPDATE ZZJunk SET ID = ID +1 " & _
"WHERE ID Between " & lngNew & " AND " & varOld
End If
DBEngine(0)(0).Execute strSQL
Me.txtID = lngNew
Me.Dirty = False 'save record with new number

Me.Requery
With Me.RecordsetClone
.FindFirst "ID=" & lngNew
Me.Bookmark = .Bookmark
End With

End Sub
 
Allen and Marsh,

Thank you both for your ideas. I hope to have a chance to try them
tonight. Unfortunately for me I have a class today and will not get to try
them out right away. I appreciate the help and guidance.

Vanya
 
Back
Top