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?
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?
ALL values in the table or just 1 particular record?
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
What happens when you get to row 12 - is it switched with row 11 or do you do every second row?
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.
In the form, put a button to swap...
Code:btnSwap_click() I =txtSortOrder TxtSortOrder = I+1 docmd.gotorecord,,acNext txtSortOrder = I End sub
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).
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