Results 1 to 8 of 8
  1. #1
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79

    Switch two values in a field

    Hi,



    I have a table named SkillT. In the table I have a column (field) named SortOrder. How can I switch values in row i with row i+1 in column SortOrder by VBA code?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    ALL values in the table or just 1 particular record?

  3. #3
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Only the values in field SortOrder on row i swith with row i+1.

    For instance if i =10:

    Before:
    Row 10 = 25
    Row 11 = 26

    After:
    Row 10 = 26
    Row 11 = 25

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What happens when you get to row 12 - is it switched with row 11 or do you do every second row?

  5. #5
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    What I really have is a table with a column SortOrder. SortOrder is tracking how I want to sort the table because I don't want to sort either ascending or descending. The table is shown in a ListBox in a Form. When I select one record in the listbox, and the press a commandbutton (Down), I want the selected record to move down one row and switch place with the record below. My thought was to switch the number in the SortOrder field (Just these two). Then execute the sort and update my listbox. Maybe there is a beter way doing this.

    So I just wanted help to get the code by swithing number in SortColumn for these two records.

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In the form, put a button to swap...

    Code:
    btnSwap_click()
    I =txtSortOrder
    TxtSortOrder = I+1
    docmd.gotorecord,,acNext
    txtSortOrder = I 
    End sub

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I am thinking that you may have to make it a subform instead of a list box - move next, get sort order, update that record, move previous, update that record.

    There is also a record number associated with each record (as you see at the bottom of the list: record x of y).

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    No, you can use a listbox. Here is some code, change it as required.

    Code:
        Dim NewX As String, OldX As String
        OldX = Me!List0.Column(2)
        
        NewX = DLookup("Min(Field1)", "Table2", "Field1>'" & OldX & "'") 'get next record's number
        DoCmd.RunSQL "UPDATE Table2 SET Field1='" & OldX & "' WHERE Table2.Field1='" & NewX & "';" 'update next record
        DoCmd.RunSQL "UPDATE Table2 SET Field1='" & NewX & "' WHERE Table2.Prod_ID=" & Me!List0 & ";"  'update first record
        
        Me!List0.Requery

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Switch Formula & Between two values
    By DavidQMP in forum Access
    Replies: 4
    Last Post: 04-07-2016, 07:52 AM
  2. Replies: 18
    Last Post: 03-26-2015, 07:26 AM
  3. Replies: 13
    Last Post: 03-12-2015, 05:55 PM
  4. Replies: 1
    Last Post: 02-19-2014, 11:07 AM
  5. Replies: 2
    Last Post: 06-09-2011, 12:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums