CodeDocu.com

 

 

 

This vba code example shows how to reorder a form in vba with a serial number.

When modifying the sort number lfdNr, a copy of the form's recordset is pulled in the background with vba code.

The copy is copied to a recordsetclone, sorted, and filtered. In the Recordsetclone, all new numbers are changed and stored in ascending order.

After changing the sorting numbers, the form is re-sorted and loaded and the bookmark cursor is set to the new position.

 

Access: Sort and Filter Recordset and Recordsetclone

 

 

Public Sub fg_ReSort_LfdNr()

    '----------------< fg_ReSort_LfdNr() >----------------

    '< bookmark >

    Dim ID As Long

    ID = ID_Position

    Dim actBookmark

    actBookmark = Me.Bookmark

    '</ bookmark >

 

    '< save change >

    Requery

    '</ save change >

 

    Dim actNr As Integer

    actNr = 0

    '----< Clone Recordset_Data >----

    Dim recClone As Recordset

    Set recClone = Me.RecordsetClone

   

    '< Sort Filter >

    recClone.Sort = "lfdNr ASC"

    '</ Sort Filter >

 

    Dim intLast As Integer

    '--< @Loop: Clone >--

    Set recClone = recClone.OpenRecordset

    If Not recClone.EOF Then recClone.MoveFirst

    Do Until recClone.EOF

        If recClone("ID_Position") = ID Then

            '-< current >-

            If actNr = recClone("lfdNr") Then

                '< set new Nr >

                actNr = actNr

                recClone.Edit

                recClone("LfdNr") = actNr

                recClone.Update

                '</ set new Nr >

 

                '< push previous >

                actNr = actNr + 1

                recClone.MovePrevious

                recClone.Edit

                recClone("LfdNr") = actNr

                recClone.Update

                recClone.MoveNext

                '</ set new Nr >

 

                intLast = actNr

                '</ push previous >

            Else

                '< set new Nr >

                actNr = actNr + 1

                recClone.Edit

                recClone("LfdNr") = actNr

                intLast = actNr

                recClone.Update

                '</ set new Nr >

            End If

            '-</ current >-

        Else

            '< set new Nr >

            actNr = actNr + 1

            recClone.Edit

            recClone("LfdNr") = actNr

            intLast = actNr

            recClone.Update

            '</ set new Nr >

        End If

 

 

        '< next >

        recClone.MoveNext

        '</ next >

    Loop

    recClone.Close

    '--</ @Loop: Clone >--

    '----</ Clone Recordset_Data >----

 

    DoCmd.SetOrderBy "lfdNr"

 

    '--< set Bookmark-Cursor >--

    Dim rec As Recordset

    Set rec = Me.Recordset

    Do Until rec.EOF

        If rec("ID_Position") = ID Then

            Exit Do

        End If

        rec.MoveNext

    Loop

    '--</ set Bookmark-Cursor >--

 

    '----------------</ fg_ReSort_LfdNr() >----------------

End Sub

 

 

 

 

 

Simple code for sorting and filtering the data copy

Public Sub fg_Sort_LfdNr()

    '----------------< fg_Sort_LfdNr() >----------------

 

    Dim actNr As Integer

    actNr = ctlLfdNr

    '----< Clone Recordset_Data >----

    Dim recClone As Recordset

    Set recClone = Me.RecordsetClone

   

    '< Sort Filter >

    recClone.Sort = "lfdNr"

    recClone.Filter = "lfdNr >" & actNr

    '</ Sort Filter >

    '--< @Loop: Clone >--

    Set recClone = recClone.OpenRecordset

    recClone.MoveFirst

    Do Until recClone.EOF

        actNr = actNr + 1

        recClone.Edit

        recClone("LfdNr") = actNr

        recClone.Update

        '< next >

        recClone.MoveNext

        '</ next >

    Loop

    recClone.Close

    '--</ @Loop: Clone >--

    '----</ Clone Recordset_Data >----

 

    '----------------</ fg_Sort_LfdNr() >----------------

End Sub

 


Software Entwicklung Stuttgart NĂ¼rtingen
Suche Projekte C#, WPF, Windows App,ASP.Net, vb.Net, WinForms, SQL Server, Access, Excel