How do I manually re-order a field list - It's a Tough one...

[efandango]

I want to create a form that allows a user to re-order the field listing
manually based on an order number selected by the user via a button(s)
associated with each field, that the user would press to either send the
field UP or DOWN the order. (the same way as the TAB REORDER function in
access works) If the user pressed the down button 3 time, then the field
record would move down 3 places in the overall order, and if he pressed the
UP button x times, it would go up the order x times.

How would be the best way to approach this problem?

an example, list of fruits:
Field 1: Apples Order 1
Field2: Pears Order 2
Field 3: Oranges Order 3
Field 4: Lemons Order 4

Would become,
Field2: Pears Order 1
Field 1: Apples Order 2
Field 4: Lemons Order 3
Field 3: Oranges Order 4

 

[KARL DEWEY]

Do you really have different fruit in separate fields?

Here is a method to do what I think you want. In you form create an
option group with buttons for field1 up, field1 down, field2 up, field2
down, field3 up, field3 down, etc. Use default of zero. Have all up sorts
be odd and down be even.

Add two fields in your query like this --
Up_Sort: IIF([Forms]![YourFormName]![Frame1] =1, [Field1],
IIF([Forms]![YourFormName]![Frame1] =3, [Field2],
IIF([Forms]![YourFormName]![Frame1] =5, [Field3],
IIF([Forms]![YourFormName]![Frame1] =7, [Field4], Null))))
Set Sort to Ascending.
Use criteria of 1 Or 3 Or 5 Or 7 in the first criteria row.
Create a Down_Sort field use frame number equal to 2, 4, 6, and 8.
Set Sort to Descending.
Use criteria of 2 Or 4 Or 6 Or 8 in the second criteria row.

 

[efandango]

actually the fields are just fruits for convenience and simplicity, they will
eventually be street names (unique). about 20 fields.

I don't really follow your example below. regarding the option group, i
imagined just two buttons next to each field, an up or down button. But if I
use option group they all stay together in one big group, and I end up with a
40 button panel (2 x 20 fields)


I also don't entirely understand your If scenario below, what is the
[frame1] field that you refer to?, i also don't follow the use of two
criteria in the criteria row, how do I make sure that the correct criteria is
chosen via the buttons?

Do you really have different fruit in separate fields?

Here is a method to do what I think you want. In you form create an
option group with buttons for field1 up, field1 down, field2 up, field2
down, field3 up, field3 down, etc. Use default of zero. Have all up sorts
be odd and down be even.

Add two fields in your query like this --
Up_Sort: IIF([Forms]![YourFormName]![Frame1] =1, [Field1],
IIF([Forms]![YourFormName]![Frame1] =3, [Field2],
IIF([Forms]![YourFormName]![Frame1] =5, [Field3],
IIF([Forms]![YourFormName]![Frame1] =7, [Field4], Null))))
Set Sort to Ascending.
Use criteria of 1 Or 3 Or 5 Or 7 in the first criteria row.
Create a Down_Sort field use frame number equal to 2, 4, 6, and 8.
Set Sort to Descending.
Use criteria of 2 Or 4 Or 6 Or 8 in the second criteria row.


--
KARL DEWEY
Build a little - Test a little

I want to create a form that allows a user to re-order the field listing
manually based on an order number selected by the user via a button(s)
associated with each field, that the user would press to either send the
field UP or DOWN the order. (the same way as the TAB REORDER function in
access works) If the user pressed the down button 3 time, then the field
record would move down 3 places in the overall order, and if he pressed the
UP button x times, it would go up the order x times.

How would be the best way to approach this problem?

an example, list of fruits:
Field 1: Apples Order 1
Field2: Pears Order 2
Field 3: Oranges Order 3
Field 4: Lemons Order 4

Would become,
Field2: Pears Order 1
Field 1: Apples Order 2
Field 4: Lemons Order 3
Field 3: Oranges Order 4

 

[KARL DEWEY]

I made a mistake in the post - you do not need the criteria as the IFF
statements do the selection.
You are creating a spreadsheet and not using Access as is was meant to be
used. It is a relational database. Your table structure should not be one
field for each street but one record for each street. What will you have in
the records for each street?

 

[efandango]

Karl,

Perhaps it’s best if I explain fully from the beginning.
This is a form that tests peoples ability to put street names in order of
how they link to each other in the real world. The form/table is 20 blank
fields, each one is a combo box that allows the user to pick from a master
table list of hundreds of street names .

For example
Field1 Main St
Field2 South St
Field 3 East St
Field 4 Easy St
Field 5 Sunny St
and so on…
Field 20 Last St

The problem comes when a user forgets to choose a street that comes after a
given street, where depending on which street is missing, he will have to go
back and replace each field with the correct street, for example, imagine a
street is missing between [Field3] East St and [Field4] Easy St; he will have
to move East St and Easy St down a place in the order to accommodate his new
street; like this.

Field1 Main St
Field2 South St
Field 3 East St
Field 4 New St
Field 5 Easy St
Field 6 Sunny St
and so on…
Field 20 Last St

When there are 20 Streets on the form, it becomes too difficult to do, and
furthermore, these 20 fields will constitute a ‘route’; and the eventual aim
is to create a large number of different routes; so you can see that unless I
have a simple mechanism to move the field contents up and down, it will be
impossible to make this work on a larger scale.

I hope this now makes much better sense than my initial explanation, and
that there may be a way of cracking this tough nut…

 

[John W. Vinson]

When there are 20 Streets on the form, it becomes too difficult to do, and
furthermore, these 20 fields will constitute a ‘route’; and the eventual aim
is to create a large number of different routes; so you can see that unless I
have a simple mechanism to move the field contents up and down, it will be
impossible to make this work on a larger scale.

I would really suggest using a normalized structure, with a Route table
consisting of (at least) two fields: StreetID and Seq. If the proper route is
from Main St. to 3rd to Maple to Easy this table would contain

Main 1
3rd 2
Maple 3
Easy 4

The Seq field could have gaps so that you could insert another street within
an existing sequence. This will allow an arbitrary number of streets in a
route; easy resequencing; allow you to compare one route against another; and
NO problem with needing to change fieldnames in your table.

John W. Vinson [MVP]

 

[efandango]

John,

I think you may slightly have the wrong impression of what I am trying to
do. I suspect that you may be thinking that this form/table is supposed to be
a normal/traditional setup, in the sense that it is for ‘record keeping’,
when actually it is supposed to be for ‘playing’ with, in the way previously
described where the user has the facility to say, “oops!, I have selected
that street in the wrong place, I should have put it two places further down
the order. No problem, I will simply click the up or down button as
appropriateâ€, bear in mind that the user will never be typing these streets
into the boxes, he will simply open the form with blank fields and start
‘building’ the route. This is intended for people that do not use a database
and will not want to get into entering anything such as sequence numbers.

The structure for the table is already normalised.

Street_Name_Joins_ID Autonumber
Street_Name_Joins_Name Text
StreetName1 Text
StreetName2 Text
StreetName3 Text
StreetName4 Text
StreetName5 Text
StreetName6 Text
And so on…

I understand the merit of having gaps in the seq field, but am not sure how
that would be affected by the user. I don’t want or need to change actual
fieldnames, just the contents of the fields relative to the order. From
Access’s point of view, the fieldnames are ‘almost’ meaningless, but
obviously for human reading they appear orderly. They can be called anything,
the only thing that is paramount is the order of their contents.

Going back to the original post/conception, this form has to give the user
the ability to move a given field’s contents from one field to another, up
or down with a button click, and move the ‘replaced’ incumbent field’s along
the order respectively.

 

[BnB]

I'm not going to worry about why you're doing what you're doing, but here's
one way of doing it. Use a list box and have a table with a query that serves
as the row source. The table would have two fields, the street name and the
order. The list box would only display the Street field. When the user
selects a street, use VB to add it to the table and put the "next" order
number in the order column. So if Streets Main, 3rd, and Cherry were selected
in that order, the table would be

Street Order
-----------------
Main 1
3rd 2
Cherry 3

(If this is HTML, my formatting may get screwed up when this posts...
apologies if so)

When the user selects a row in the table, you can access the associated
record in the table in VB, and then adjust the order number up or down by one
with each click of the button. You then have to use VB to change all the
other order numbers, which is a little clunky and brute force, but for only
20 it's not that bad. You'll need a query for the row source since you want
to use "order by" on the order number. You can then refresh the list after
each table reordering.

Using this, any entry can be moved up or down. (Need to make sure you stay
within the obvious order limits, of course) Not sure if this accomplishes
exactly what you want, but it's something...

BnB

 

[efandango]

I follow the theory, I have the table as you describe, and am using a query
of that for the row source to use 'order by' on the order number

but am struggling with the VB side of things, particulary on the 'putting
next order number'; at the moment I have a simple macro setup that changes
the order value box. I also, can't get my head around the vb code for the
brute force part of the process where it changes all the other numbers, can
you give me some pointers on the VB structure (I can use VB, but not very
good at concieving the code)

 

[BnB]

You know... it's much easier than I thought since you're only moving by one
(I hadn't really thought through an algorithm last night). If the user wants
to move it up further it doesn't matter, since it will happen one click at a
time, so you only need to write code for moving up or down one. If you want
to move the 12th item up, you change its order from 12 to 11, then search
through the table for the other one that's 11 and change it to 12. You're
just swapping places with what used to be the one above. The reverse going
down. The same rule holds regardless of direction: just exchange order
numbers with the next item in the direction you want to go.

The brute force part is searching through all 20 items to find things rather
than having some other more clever way. There are a couple ways of
approaching this, so I actually tried it. I wanted to be able to use the
query as the RecordSet source, since then the two records that need to be
changed would be next to each other. But as I feared, because you're editing
the field that's the "OrderBy" field, as soon as you change the order, the
recordset may shuffle, and it's unpredictable whether the OTHER record with
the same order will be ahead of or behind the current one. There are ways
around that, but I went back to using the table as the RecordSet source.

So I got it to work; here's the code from the Form code module (so Me refers
to the form). I use "!" only for fields, preferring "." for everything that
can use that. I eschew Hungarian notation (I typically create lots of classes
and end up needing a lookup table to figure out all the prefixes, which
defeats the purpose for me), so I've just used plain names. I also use DAO
since I've always found it simpler. But you have to set a reference to use it
using Tools/References. If you use ADO, then you'll need to adapt the code. I
called the listbox RouteList, and the buttons UpButton and DownButton.

Hope this helps.
BnB


Private Selected As DAO.Recordset 'points to current table selection
Private SelectedRow As Integer 'for readability


Private Sub Form_Open(Cancel As Integer)

'open the table; use dynaset so .FindFirst works
Set Selected = CurrentDb.OpenRecordset("Order Table", dbOpenDynaset)

End Sub


Private Sub RouteList_Click()

'record the row selected for readability and then find the associated
record
SelectedRow = Me.RouteList.ListIndex
Selected.FindFirst "Street = '" & Me.RouteList.Column(0, SelectedRow) &
"'"

End Sub


Private Sub UpButton_Click()
Dim ThisStreet As String 'for readability
Dim ThisOrder As Integer 'for readability

'if at the top of the list already do nothing
If Me.RouteList.ListIndex = 0 Then Exit Sub

With Selected
ThisStreet = !Street 'keep the selected street name

'decrement the order on the selected street
.Edit
!Order = !Order - 1
.Update

'keep the new order number and find the OTHER record that has that
order
ThisOrder = !Order
.FindFirst "Order = " & ThisOrder & " AND NOT Street = '" &
ThisStreet & "'"

'increment the order on that record
.Edit
!Order = !Order + 1
.Update

'go back to the original selected record
.FindFirst "Street = '" & Me.RouteList.Column(0, SelectedRow) & "'"
End With

'requery the list and note that the selected row has been decremented
Me.RouteList.Requery
SelectedRow = SelectedRow - 1

End Sub


Private Sub DownButton_Click()
Dim ThisStreet As String 'for readability
Dim ThisOrder As Integer 'for readability

'if at the bottom of the list already do nothing
If Me.RouteList.ListIndex = Me.RouteList.ListCount - 1 Then Exit Sub

With Selected
ThisStreet = !Street 'keep the selected street name

'increment the order on the selected street
.Edit
!Order = !Order + 1
.Update

'keep the new order number and find the OTHER record that has that
order
ThisOrder = !Order
.FindFirst "Order = " & ThisOrder & " AND NOT Street = '" &
ThisStreet & "'"

'decrement the order on that record
.Edit
!Order = !Order - 1
.Update

'go back to the original selected record
.FindFirst "Street = '" & Me.RouteList.Column(0, SelectedRow) & "'"
End With

'requery the list and note that the selected row has been decremented
Me.RouteList.Requery
SelectedRow = SelectedRow + 1

End Sub

 

[John W. Vinson]

Going back to the original post/conception, this form has to give the user
the ability to move a given field’s contents from one field to another, up
or down with a button click, and move the ‘replaced’ incumbent field’s along
the order respectively.

Very easily done in VBA code, by having the arrow button code reassign the
sequence number (which need not even be visible).

Very much harder if you are reassigning fieldnames.

John W. Vinson [MVP]

 

[efandango]

John,

I don't want to do it with fieldnames, just the data. Can you elaborate on
how easy it is to reassign the sequence number(s)?


I have sucessfully managed to do it with macros, but would rather a cleaner
solution in code

 

[John W. Vinson]

John,

I don't want to do it with fieldnames, just the data. Can you elaborate on
how easy it is to reassign the sequence number(s)?

You say

The structure for the table is already normalised.

Street_Name_Joins_ID Autonumber
Street_Name_Joins_Name Text
StreetName1 Text
StreetName2 Text
StreetName3 Text
StreetName4 Text
StreetName5 Text
StreetName6 Text

I disagree. That is NOT properly normalized, and my ideas would not apply.

Is that in fact still your table structure?

I have sucessfully managed to do it with macros, but would rather a cleaner
solution in code

John W. Vinson [MVP]

 

[efandango]

When I try yo run the code, i get this error message:

Compile Error:
Method or Data Member not found

and it highlights 'RouteList' in this line of code:

If Me.RouteList.ListIndex = 0 Then Exit Sub

what does the error mean, and what exactly is 'RoutList'? is it supposed to
be the name of my form or a field on the form?

 

[efandango]

I think I figured out that 'RouteList' is the name of the field with the
streetname in it, but now I get this error message:

Run-Time Error 424
Object Required

and the VBA window highlights the following line:

ThisStreet = !Street 'keep the selected street name

 

[efandango]

John,

I have since normalised my table which now reads:

Street_Name_Joins_ID Autonumber
StreetName Text

I use a combo box linked to a Streetnames master table to add records to my
'tbl_Street_Joiner' table. Can you tell me how it is "Very easily done in VBA
code, by having the arrow button code reassign the sequence number (which
need not even be visible)." and do I need any other fields such as 'order no'?