Up and down in a list box (2 Viewers)

[ClaraBarton]

@arnelgp I've used your Export and Import form with a lot of modifications for years but there's a problem that bugs me that I can't figure how to fix.

The right listbox is called lstSelected. If the fields are randomly selected (not in consecutive order) the up and down button has to go through the unselected fields in the table in order to move. So you have to click, click, click to get it to move.

Public Function fnMove2(ByVal i As Integer)
    Dim db              As DAO.Database
    Dim lValue          As Long
    Dim iOrder          As Integer

Set db = CurrentDb
With Me.lstSelected
    If .ListCount = 0 Then Beep:     Exit Function
    If .ListIndex < 0 Then Beep:     Exit Function
       
    iOrder = DLookup("Order", "zzTable", "ID = " & .Value)
    lValue = Me.lstSelected.Value
    If i = 1 Then   'move up
        If iOrder > 1 Then
            With db
                .Execute "update zzTable set [Order] = 9999 where [Order] = " & iOrder
                .Execute "update zzTable set [Order] = " & iOrder & " where [Order] = " & iOrder - 1 & ";"
                .Execute "update zzTable set [Order] = " & iOrder - 1 & " where [Order] = 9999;"
            End With
        End If
    Else           'move down
        If iOrder < DMax("Order", "zzTable") Then
            With db
                .Execute "update zzTable set [Order] = 9999 where [Order] = " & iOrder
                .Execute "update zzTable set [Order] = " & iOrder & " where [Order] = " & iOrder + 1 & ";"
                .Execute "update zzTable set [Order] = " & iOrder + 1 & " where [Order] = 9999;"
            End With
        End If
    End If
    .Requery
    .Value = lValue
End With

Set db = Nothing
Call fnReOrderColumns

End Function

It seems to me that the Order field should only contain a number if the Selected field is true. I've tried various things with the function that fills the zzTable

Public Function fnUpdateZZTable(ByVal qName As String)

    Const TABLE             As String = "zzTable"
    Dim db                     As DAO.Database
    Dim rst                    As DAO.Recordset
    Dim fld                   As DAO.Field
    Dim i                      As Integer
    Dim strSql              As String
   
    Set db = CurrentDb
    strSql = "DELETE * FROM zzTable"
    db.Execute strSql
   
    If Len(qName) <> 0 Then
   
    Set rst = CurrentDb.OpenRecordset(qName)
         
        For Each fld In rst.Fields
          i = i + 1
        strSql = StringFormatSQL("INSERT INTO [zzTable] ([FieldName], [FieldType], [Selected], [Order]) " & _
                    " SELECT {0}, {1}, {2}, {3};", _
                      fld.Name, fnGenType(fld.Type), 0, i)

           db.Execute strSql
        Next
    End If
   
    Set rst = Nothing
    Set db = Nothing

End Function

Since the Selected Field is not actually in the recordset, I can't figure how to do this. Or is there some other way I could accomplish this?

 

[arnelgp]

if you can post a strip version of the db, someone or i can help it understand better.

 

[ClaraBarton]

Open popExport. Move FullName, CkDate, Debit, Credit to selected box. Now move Debit to the top of the list. See how you have to Click, Click, Click to get it to move?