Re-ordering a Numeric Field

[Anthony Viscomi]

*Please note- this is a re-post*

I have a dillemma; my client is a cabinet maker and the database
requirements that he initially supplied to me were vague Until a majority
of my development was done. Here is what he has "sprung" on me.

I have a table with a numeric field named "Position" this field can contain
a whole number (1, 2, 3, etc) which represents the position that a cabinet
would be placed within a row of cabinets. The field can also contain a
decimal (1.1, 2.3, 3.1, etc.) the decimal value represents any
accessory/add-on for the cabinet in the referenced position.

My dillemma is that if the user decides they want to add a position
(cabinet) I need a way of resquencing all of the subsequent positions
(cabinets). In other words; there may be a position number 2 along with
various add-ons 2.1, 2.2 and so on. The user decides they want another type
of cabinet for position 2, thus they create another position 2. I need a way
for everything that follows to be re-ordered.

The "Position" value is entered via a SubForm with is linked to the MainForm
(Order tbl) via an unique OrderID, thus my first cirteria would be Where
SubfrmID=MainfrmID do something. I had planned on placing this re-ordering
event behind a cmdButton.

Can anyone please assist?
Thanks in advance!

Anthony

 

[Marshall Barton]

*Please note- this is a re-post*

I have a dillemma; my client is a cabinet maker and the database
requirements that he initially supplied to me were vague Until a majority
of my development was done. Here is what he has "sprung" on me.

I have a table with a numeric field named "Position" this field can contain
a whole number (1, 2, 3, etc) which represents the position that a cabinet
would be placed within a row of cabinets. The field can also contain a
decimal (1.1, 2.3, 3.1, etc.) the decimal value represents any
accessory/add-on for the cabinet in the referenced position.

My dillemma is that if the user decides they want to add a position
(cabinet) I need a way of resquencing all of the subsequent positions
(cabinets). In other words; there may be a position number 2 along with
various add-ons 2.1, 2.2 and so on. The user decides they want another type
of cabinet for position 2, thus they create another position 2. I need a way
for everything that follows to be re-ordered.

The "Position" value is entered via a SubForm with is linked to the MainForm
(Order tbl) via an unique OrderID, thus my first cirteria would be Where
SubfrmID=MainfrmID do something. I had planned on placing this re-ordering
event behind a cmdButton.

This basic function can be performed by executing an Update
query something like:

UPDATE table
SET Position = Position +1
WHERE Position >= 2
AND OrderID = 12345