When a seq no is deleted, modify the remaining ones

[lorirobn]

Hi,

I have a form where a user can delete a record from an Items table. On the table is a field for sequence number. Let's say there are 3 of the same item (seqno 1, 2, and 3), and sequence number 2 is deleted. That leaves me (incorrectly) with seqno 1 and seqno 3 on the table. I'd like to change seq no 3 to seq no 2 (and if any more existed, modify them as well).

Does anyone have suggestions on how I can code, at the time the record is deleted, to modify the remaining seqno records?

I am just getting comfortable with VBA, and could probably (eventually!) figure it out on my own, but it would be really great to have suggestions to help me along.

In case you want more information about the table, it contains all Items for a given room number. If the room has multiple occurrences of the same item number (ie, specific bed), the seq no identifies which one.

Thanks,
Lori

 

[Crowley16]

how is the sequence number generated?

although what you're asking is possible, it's not really a very reliable way of doing things...

what you can do is to have a unique id of each item, and assign them to rooms n number of times, and then instead of creating and saving a sequential number, just generate a dynamic number, probably in a query of some sort. This way you wouldn't have to re-sequence anything, just remove the reference of the item Num in the room Num...

--------------------
Procrastinate Now!

 

[lorirobn]

I generate the sequence number at the time the record is added, using a DMAX to get the highest existing sequence number.

How would you suggest having a unique id of the item? Would you automatically generate it? Hmmm...that is an idea. Then I could just list the item with its quantity on reports and such.

 

[Crowley16]

presumably you have an item table, and a room table, and you assign items to rooms by a link table with itemID and roomID and maybe a quantity field specifying how many items of that type in a room...

then you would have 2 unique keys, both auto generated by the system (autonumber) or customer keys generated upon creation of the record in the table...

then on reports you can list items per room, and have a counter field which just increments, grouped by items, then rooms...
you might have to have some code to generate this increment on the report but there would be no need to resequence anything...

--------------------
Procrastinate Now!

 

[lorirobn]

I have most of what you presume (Item table, Room table, and link table with item id and room id). However, instead of having a quantity field, i have items repeat, and that's where the sequence number came in.

I am going to think about having quantity instead...

Thanks for your help,
Lori