Editing a recordset before closing data entry form

[bentheimmigrant]

Couldn't decide if this belonged in here, or queries, or vba...

I've got a form to add an item to a sequence. The user can pick the position in the sequence. However, if there is already an item at that point in the sequence, it needs to be shunted along.

The data entry form has a bound textbox which automatically gets populated with the position in the sequence. I'm writing a routine to go through and add 1 to each item in the sequence, starting with the one to be shunted.

However, what I'm not clear on is how new records are stored. If I'm adding a new record with position n, and I go to change the existing position n to n+1, will I end up with two n+1's (i.e., old item and my new item)? Or will it not matter, since I've got that value populated in the bound textbox, and so even if I accidentally change it to n+1, it will be returned to n when I close the form?

I hope that makes sense. TIA.

 

[arnelgp]

on new record, it will be changed to whatever you typed in whether deliberately or not.

 

[jdraw]

Also note, that there is no inherent order/sequence/position in the table. You can set the Order/sequence by using a query with an Order By clause.
So it may be that you'll need to add the field value and also a value to the field on which you do your "sequencing".

 

[bentheimmigrant]

on new record, it will be changed to whatever you typed in whether deliberately or not.

Right. But if I then query all records in that sequence, and add 1 to all values where the sequence position is >= n, will I end up incrementing my new entry, and if so, will this then be returned to n when I close the form (since I'm not updating the contents of the bound textbox)? Or has the routine superseded the typed value?

Also note, that there is no inherent order/sequence/position in the table. You can set the Order/sequence by using a query with an Order By clause.
So it may be that you'll need to add the field value and also a value to the field on which you do your "sequencing".

Yes, we have a field for position in sequence, and my routine orders by that. Then I loop through with If Position >=n... But is my new record already in that recordset (I assume, since this is a bound form, running an On Close event)?