Moving records to a specific position in a recordset (1 Viewer)

[MStCyr]

This is used to move a record to a specific location within a recordset. As the form opens, you will notice that Project 1 is the first item in the list.

By clicking Move Down, the record will move to a new location.

This routine came out of a project management project. This allowed to place the projects in order of importance.

 

[kuifje_007]

I have a question about your excellent program about MoveRecords.

On the MoveUp-button there is a code:

 Set frm = Me![Report Detail].Form

I have a form with 2 listboxes. The data in the first listbox I need, I move it to the second listbox. And in the second listbox I want to move or down the datas. So I don’t have a subform. What should I use instead of Set frm = Me![Report Detail].Form ?

I tried with something like Me!Listbox2, but it doesn't work.

Thanks in advance.

 

[MStCyr]

I have a question about your excellent program about MoveRecords.

On the MoveUp-button there is a code:

 Set frm = Me![Report Detail].Form

I have a form with 2 listboxes. The data in the first listbox I need, I move it to the second listbox. And in the second listbox I want to move or down the datas. So I don’t have a subform. What should I use instead of Set frm = Me![Report Detail].Form ?

I tried with something like Me!Listbox2, but it doesn't work.

Thanks in advance.

In this case, with the use of 2 listboxes you could possibly use the sample that I posted before:
http://www.access-programmers.co.uk/forums/showthread.php?t=107665

where you could modify this sample as per the MoveRecord sample.

 

[Keith Nichols]

Awesome!

Hi MStCyr,

Very impressed and I intend to implement something based on this in my DB.

The twist is that I want to be able to reset the numbers in the order list on the click of a button so that the order runs from 1 onwards sequentially. Thus, If there are any gaps in the list they get deleted

One of the reasons for this is slightly more than cosmetic. When viewing a project, I would like to be able to see its 'absolute' priority rather than a meaningless number. eg. if a project shows priority 16, I would like there to be 15 projects with higher priorities. Similarly, I rank employees to appear in telephone listings in a certain order and would like a gap free list.

I have had a play about but I am pretty green at all this and don't really know how close I am to success:- see code below

Any pointers would be greatly appreciated .

Private Sub cmdResetOrderList_Click()
On Error GoTo ErrHandler

Dim db As Database, rs As Recordset
Dim frm As Form
Dim i As Integer

    Set frm = Me![Report Detail].Form
    Set rs = frm.RecordsetClone
    
    If rs.RecordCount = 0 Then
        Exit Sub
    End If
    
    rs.MoveFirst
    
    
For i = 0 To rs.RecordCount - 1
        rs("Order") = i + 1

Next i

 

[MStCyr]

Good afternoon

Sorry for the tardy respose, I've been away for a few days. I've tried coding the app for resorting the columns, however this seems to affect the Bookmark.

The best way to sort the columns is to place the cursor in the field - i.e. Order, then right click then select Sort Ascending or Descending

 

[Keith Nichols]

Hi MStCyr,

Thanks for the response. It wasn't really an active thread so I didn't expect anything immediately.

I have been further messing around with this and searching the forum. For one of my uses (ranking personnel for telephone listing etc) the number is meaningless so I can simly not display it. There will be no consequences for any gaps.

For the other use, assigning priority to projects, the number would have meaning. There is a method of resetting a number list posted by Pat Hartman on this thread which would do the trick.
http://www.access-programmers.co.uk/forums/showthread.php?t=97888&highlight=renumber+list

I will have a go at this ove the next week or so.

One slight modification to your sample: I set the Auto Repeat of the move up and down buttons to yes and put a pause of 150 milliseconds in the subroutine. Thus, if you want to move an item a numer of positions, you can hold the button down and the record moves up or down the list nicely.

Regards

 

[Keith Nichols]

Lists within lists

Hi MStCyr

This version orders a list within a list.

The main form has a combo box used to select a sub grouping of the record set, in this case all the individuals in a particular department, and allows you to position them within that subset. This is used to make the entries appear in the correct order for combo boxes and telephone listings throughout the database where everything is either sorted or filtered by department prior to being sorted by telephone rank.

When new employees are added, they have a default telephone rank of 0 putting them at the top of the listing for their department so no special code is required to give them a number when created. If it transpires that 2 records (employees) have the same rank number, then the up down buttons will not function correctly. In this case, it is a simple matter to edit the rank directly and then reposition the employee on the list using the buttons. The absolute numbers of the telephone rank are irrelevant, but for neatness they can be edited to show no gaps. In my DB, I set the rank for certain items to 91 through to 99 and use this as a filter to exclude the entries for where they are not appropriate such as 'none-employee' entries for the kitchen and so forth.

All credit to MStCyr who developed the original. I have tried to remove any none essential code to make it easier to follow, but confess here that I am not 100% on how it all works. I mostly got it working by slavishly replacing the original terms with my db object names and then deleting things that did nothing (redundant code form the original DB). But it does work!


Regards,