How to identify an adjacent record of one marked for deletion?

[Dave]

This is a rather tricky problem.

For purposes of illustration assume I have an order main form with an
embedded orderdetail subform. I can from the main form click a button and
open a popup form with list pf parts available for order. The business rule
is that there can be only one instance of a part per order. As I scroll
through the list of parts in the popup form I click a button and post
(assign) the part to the order. As soon as the part is posted to the order,
it is removed from the popup form's record set (and display) to preclude any
subsequent posting of the part to the current order.

To further illustrate, assume I open my order form, create an order, and
then open my popup form with its list of parts. I click on one of the 5
parts displayed which is then "posted" to the order form (i.e., it
disappears from the popup list and appears in the subform of the order
form.) If I repeat this action with the other 4 parts, the popup list will
eventually be empty and there will be 5 parts in the subform of the orders
form.

All this works as it should.

The part I am struggling with is how I can preserve my position in the
recordset of the popup form after I post to the orders form. Say instead of
5 parts I had 500 parts and I just posted part 425. Record 425 is deleted
from the popup form's recordset, the form is requeried and I am positioned
on record 1. Instead, I would like to be positioned on record 424 so I
don't have to re-scroll through the entire list of parts to get to the
section I was most recently working with.

What is the best way to approach this problem?

I have played with the recordsetclone property and the moveprevious method
to try to identify and preserve the record adjacent to the one being posted
(i.e., the one that will be deleted). However, I've not got anything to
work properly. Besides, my approach seems like something of a hack.

Does anyone have any suggesstions?

 

[Albert D. Kallal]

I would think the program logic would be like:

users selects item from popup form....

code runs to move the record to the sub form

grab current position in the popup form. you can go:

lngCurPos = Me.Recordset.AbsolutePosition

now run code to remove from the selection list (you actually likely don't
have code, but a select that excepts the sub form guys, so you just
requery).

...so you you then requery the popup form:

Me.Requery
me.recordsetclone.MoveLast
if me.recordsetclone.RecordCount > 0 then

if m.recordsetClone.RecordCount - 1 < lngCuirPost then
lngCurPost = me.RecordSetClone.RecordCount - 1
end if

Me.Recordset.AbsolutePosition = lngCurPos
end if

 

[Dave]

Albert:

Much thanks.

I had struggled with that issue for a long time and could not get it to
function properly. It works very nicely now.

Thank you again for your help.

Dave