Sorted Datasheet

[RonL]

I'm getting back to an old project, and I have a general (vba recordset?) approach type question if I may. The user wants to see entries on screen as a datasheet - which will be a filtered sub form with a query recordsource - with new records appending at bottom. So I'm planning a sort field, wherein the new record will be incremented from the current record. (The user will be simply tabbing through bound controls, creating an appendation automatically when tabbing to first control of a new record.) But I will also be trapping (for a key combination) that says the user wants an "insertion " not an "append" - ie. where the new record will be "in the middle". So after computing its new sort field value, all "further down" records will need the sort field incremented by one, starting with the record just following the original current record - ie. the one beneath which the user has signaled for an insertion.

I'm assuming this needs to be done by defining and looping through an appropriate recordset, incrementing the order-by field each time (doing a re query at end of loop), but would appreciate comments or better ideas from experienced coders. Thx, Ron

[ItsMe]

I would probably use a control's on enter event to determine where, within the recordset, the user is. Maybe an If Then Else statement to determine if the record is new using the Me.NewRecord method.

As for determining the greatest value for the field being used for sorting... I suppose a DMax function would be in order. I would consider using the form's recordset and cloning it to determine the max (in the interest of performance). However, the form's recordset may be filtered in a way that it does not provide the actual Max value you are looking for.

[ranman256]

There's no such thing as 'insertion', it's an append.
The sort is anyway you want.

[RonL]

Thanks for the thoughts so far. ItsMe, I like your first thought; I think you're suggesting there's no real need to trap for an "insertion." Will consider. As for the D... functions, thought I've heard they can be slow. Not sure any sort of lookup would be necessary here, because if the thing is set up right, the sorting field of the new record will always be an increment of the current record's sorting field, regardless of where the cursor is in the set.

Ranman, I think you allude to the fact that database records are analogous to blocks dropped in a bucket and you can't sort them unless they are numbered on one face. (Apologies to whoever first posted that analogy, but it's helped me and I wish the point were emphasized more for (us non-trained) newbies). But the user wants the records to appear ordered on screen as he prepares it. (It's a chronological series of events.) That's why I put quotes around words like "insertion." So of course I need the sort. The question is what's the best VBA strategy to simulate an insertion? Thanks, -Ron