Continuous forms - moving records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
Access 2002. Have a one to many form/subform. For each item many sub items
are listed. Would like to insert a record in the midst of other records much
like an 'Insert' in Excel. Or move the placement of a record much like
setting the tab order by dragging the control to the desired order.
Possible? is there an ActiveX control that would do the job?
 
Hello,
Access 2002. Have a one to many form/subform. For each item many sub items
are listed. Would like to insert a record in the midst of other records much
like an 'Insert' in Excel. Or move the placement of a record much like
setting the tab order by dragging the control to the desired order.
Possible? is there an ActiveX control that would do the job?
Click to expand...

Excel is a spreadsheet.

Access is a relational database.

THEY ARE DIFFERENT.

One way in which they differ is that in a Spreadsheet, the rows have a
defined order, independent of the data within those rows. An Access
Table is an unordered "heap" of records; there is NO SUCH THING as
"between" or "first" or "last" in a Table, in any useful way.

The *only* way to control the order of records in a Form/subform is to
have a field (or fields) within the table, and use either a Query or
the Form's OrderBy property to sort the data in ascending or
descending order by that field.

If you wish, you can put little arrow button controls on each row of
the Subform, to increment and decrement the value of this sort field,
followed by requerying the form to display the new order.


John W. Vinson[MVP]
 
hey John,

Thanks for the welcome to the user group and the commentary on the
differences between Excel and Access. I don't think you read the entire
question. If you have any further constructive ideas on the 'metaphor' of
moving or inserting records for the *user interface* I'd be glad to hear them.

Regards

Terry
 
hey John,

Thanks for the welcome to the user group and the commentary on the
differences between Excel and Access. I don't think you read the entire
question. If you have any further constructive ideas on the 'metaphor' of
moving or inserting records for the *user interface* I'd be glad to hear them.
Click to expand...

My apologies, Terry. I should not have been posting after a stressful
day; I was out of line.

One way you can do this is to have a Long Integer field Sortkey in the
table. It's handy to leave big gaps - start with Sortkeys of 64, 128,
256 and so on. You can put two small buttons on the Form, up-arrow and
down-arrow, so they appear on each record. The button would execute
code to increment or decrement the sortkey (in this example, by plus
or minus 96), detect collisions, and (if necessary) update the
collided record. It's not trivial code but it can be done.

John W. Vinson[MVP]
 
Back
Top