Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2005
    Posts
    87

    Unanswered: Moving records in the same table?

    Hello there,


    Is it possible somehow to move a record in the same table, to different row (in the form)? Or in other words, is it possible manualy adjust the position of records (rows) in a report?

    I have a continuous form, where rows are filled with data (e.g. 10 rows ).Then i have report , which looks the same as form (rows, and their order). But sometimes user wants to move certain position (row) up or down, to be it on top or bottom or two places above, or below etc, of row list....is there any easy way to do it? Like some button press, where you could swap position with next record (next row) .....

    Or just delete (everything) and fill again from beginning the way he wants...

    Thanks!

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can make a createTable query, changing the order your users need, so you will get evertime a new table ordered according to what you decide..

  3. #3
    Join Date
    Oct 2005
    Posts
    87
    But this way user will have to fill new form again from begining...? Only in different order?

    Was looking for something easier for him...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not that I'm aware of, the only guaranteed way to handle this is to use indexes, providing there is some logic behind your users requests for the different sequences then it could be done that way.

    if its just 10..20 rows then consider putting a column where the user can set a sort sequence number

    failing that Hambakka's suggestion is probably the best route, copy to a local table the rows you want, manipulate in the way you want, do waht you want and then delete the records afterwards.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2005
    Posts
    87
    I see...

    Ok then another question, is it possible to have some specific order in report from my own rule...not ascending, not descending, but let say: product X is always first and product Y always last or smth else....Can i define them somewhere

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can then Group your output using the Group facility....

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Yes, of course define the siort sequence "somewhere"

    i have seen this sort of sequencing based on product types, manufacturers etc...

    providing you can capture the users requirements for how they want to rearrange the data then you can do it easily enough

    usually its a column added to a relevant table. being dull I tenbd to call it SortSeq. Effectively it acts like a system generated number (ie its an attirbute that the user doesn't usually see or care about). Optionally it can be uniquely indexed, or not, usually don't care about it being a unique index as effectively it doesn't matter, after all its user defined so if they store the data in a cruddy manner thats their problem not yours.

    i have used this technique to arrange display of products based on a product type. so that the type code and description doens't have any system meaning
    eg
    ProdType - pk ' can be text, int, or whatever
    TypeDesc - text(50)
    SortSeq - int

    you can put it at product level. manufacturer level, a sneaky that can be used to promote manufacturers or product types is to put a space before the description (this isn't so clever if you users are expecting to select manufacturers name from a combo box).

    if you just wnat to promote a few items (ie don't wnat to have to set a sort sequence for every item then aconsider using a priority flag set to true / false. again this can be used to push certain records up or down a list
    bear in mind that indexs can be aascending or descending
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To allow the user to sort records I have used something similar to the References form. If you go to Tools/References you will see Priority with an up arrow and a down arrow. You highlight the reference you want and hit the up arrow to move it up one or down to move it down one.

    To accomplish this in Access, I added a field, SortOrder, to my table and for each item I put in the table I increment the SortOrder field by MAx+1. It doesn't really matter if it is sequential or not. In one case I have something called a Package and in every Package there are several Components. I have a Package Editor that handles the Sort Order. The user is allowed to modifiy the order of the Components in the package. So as long as each component in the package has a unique number then there shouldn't be any problem.

    To move the components up or down (there is a Component subform that is continuous and has record selectors) I select the record and press a button with an up arrow or down arrow. The code to move the records is:

    Code:
    Public Sub MoveCurrentRecord(intMove As Integer)
    
        Dim booSomethingMoved As Boolean
        Dim lngCurrentPosition As Long
        Dim lngNewPosition As Long
        Dim rstComponents As Recordset
        
        Dim lngCurrentRecordID As Long
        
        Set rstComponents = Me.sfrmEditorPackageComponentDetail.Form.RecordsetClone
        
        booSomethingMoved = False
        
        'If there are no records then exit
        If rstComponents.RecordCount <> 0 Then
        
            With rstComponents
                'Set the current record of the clone to the currently selected record
                .Bookmark = Me.sfrmEditorPackageComponentDetail.Form.Bookmark
                lngCurrentRecordID = !ComponentID
                lngCurrentPosition = !ComponentInsertionOrder
                
                If intMove = -1 Then
                    .MovePrevious
                    If Not .BOF Then
                        'Move to the previous record and update its order
                        lngNewPosition = !ComponentInsertionOrder
                        .Edit
                        !ComponentInsertionOrder = lngCurrentPosition
                        .Update
                    
                        'Now move back to the current record and set its new position
                        .MoveNext
                        .Edit
                        !ComponentInsertionOrder = lngNewPosition
                        .Update
                        booSomethingMoved = True
                    End If
                End If
                
                If intMove = 1 Then
                    .MoveNext
                    If Not .EOF Then
                        'Move to the previous record and update its order
                        lngNewPosition = !ComponentInsertionOrder
                        .Edit
                        !ComponentInsertionOrder = lngCurrentPosition
                        .Update
                    
                        'Now move back to the current record and set its new position
                        .MovePrevious
                        .Edit
                        !ComponentInsertionOrder = lngNewPosition
                        .Update
                        booSomethingMoved = True
                    End If
                End If
                
            End With
            
        End If
        
        'Cleanup
        rstComponents.Close
        
        'If you changed the order of something then update the order
        If booSomethingMoved Then Me.sfrmEditorPackageComponentDetail.Form.Requery
        
        'Now put the user back on the record that changed
        Set rstComponents = Me.sfrmEditorPackageComponentDetail.Form.RecordsetClone
        With rstComponents
            .FindFirst "ComponentID=" & lngCurrentRecordID
            If Not .NoMatch Then
                Me.sfrmEditorPackageComponentDetail.Form.Bookmark = .Bookmark
            End If
            .Close
        End With
        
        'Cleanup
        Set rstComponents = Nothing
        
    End Sub
    Then I just call MoveCurrentRecord 1 for the up arrow button and MoveCurrentRecord -1 for the down arrow button.

    This is DAO code.

  9. #9
    Join Date
    Oct 2005
    Posts
    87
    Thanks! I'll give it a try.

  10. #10
    Join Date
    Oct 2005
    Posts
    87
    Couple questions for DCKunkle:

    Can i select other way, not with record selectors?

    What is the name of sortorder in your component table? Is it "ComponentID" ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •