- Local time
- Today, 16:49
- Joined
- Feb 28, 2001
- Messages
- 31,047
If it is not OK for the order of this datasheet to change for user CharlieX while user SusieY is re-ordering things...
Make a temporary table in the FE of your database with two fields in it. Populate this table when you open the form that has the subform in it. The first field will be the PK of every entry in the table in the subform. The second field will be the copy of each record's order-by key.
Now make a query that establishes a 1:1 relationship between this temp table and the BE table that you are about to display. Bring any fields into the query that need to be displayed in the datasheet. You don't need to display any field of the table in the FE but you can still do an ORDER BY in the query to chose the order of the display.
Now when you have the subform, you automatically have a form's .Recordset AND you also have a form's .RecordsetClone.
So.... Once the user selects a row, look up the PK value. You can open the .RecordsetClone and find the selected record.
You can use the .RecordsetClone to determine the size of the recordset and can test for two special cases. If the selected record is the first record based on the sort order key, dim out the "Move Up" button. If you have selected the last record, dim out the "Move Down" button.
At the point where the user selected a row, copy the order key value to a variable in the form's Class Module declaration area. Wait for the Move Up or Move Down button.
Now, based on the button click, on the .RecordsetClone, do a .MoveNext (Move Down button if allowed) or a .MovePrevious (Move Up button if allowed). Copy the order index for THAT record.
Now do a .Edit on that record and put in the order key you copied from the record that was selected earlier. Update that record, then move back to the record where you started. Do a .Edit, update the order key from the other record. Essentially, this is an "adjacent pairwise interchange" with the added wrinkle of keeping sort order separate.
Finally, do a .Requery and .Repaint on the subform. You are in essence using the local table to have a private ordering key that is dynamically created as a 1:1 relationship for the table on the backend. When you close the form, erase the temporary table and you are done.
Make a temporary table in the FE of your database with two fields in it. Populate this table when you open the form that has the subform in it. The first field will be the PK of every entry in the table in the subform. The second field will be the copy of each record's order-by key.
Now make a query that establishes a 1:1 relationship between this temp table and the BE table that you are about to display. Bring any fields into the query that need to be displayed in the datasheet. You don't need to display any field of the table in the FE but you can still do an ORDER BY in the query to chose the order of the display.
Now when you have the subform, you automatically have a form's .Recordset AND you also have a form's .RecordsetClone.
So.... Once the user selects a row, look up the PK value. You can open the .RecordsetClone and find the selected record.
You can use the .RecordsetClone to determine the size of the recordset and can test for two special cases. If the selected record is the first record based on the sort order key, dim out the "Move Up" button. If you have selected the last record, dim out the "Move Down" button.
At the point where the user selected a row, copy the order key value to a variable in the form's Class Module declaration area. Wait for the Move Up or Move Down button.
Now, based on the button click, on the .RecordsetClone, do a .MoveNext (Move Down button if allowed) or a .MovePrevious (Move Up button if allowed). Copy the order index for THAT record.
Now do a .Edit on that record and put in the order key you copied from the record that was selected earlier. Update that record, then move back to the record where you started. Do a .Edit, update the order key from the other record. Essentially, this is an "adjacent pairwise interchange" with the added wrinkle of keeping sort order separate.
Finally, do a .Requery and .Repaint on the subform. You are in essence using the local table to have a private ordering key that is dynamically created as a 1:1 relationship for the table on the backend. When you close the form, erase the temporary table and you are done.
