Sequentially modify entries in subsequent query results.

[dlambert]

Hello,
I am trying to implement a form that i will use as part of a quotation system. The data displayed will come from a query that filters down by quotation number (selected from a combobox or similar), and orders the results based on a field called RowNumber (I use this field so that the quotation 'rows' are consistently displayed in the same order). The results will be displayed in a subform in datasheet view.
I need to implement a way to be able to select any row (entry) and press separate buttons that do the following functions:

• Add row below
  • Add an entry with the same quotation number (this is OK for me),
  • then set the RowNumber to be the next row number ( also OK),
  • then shift every row number below it so the numbers do not double up (this is the part i am struggling with)
• Delete row
  • Delete current record
  • Re-number the RowNumbers form the consequent entries to compensate for the removed row number
• Move Row Up/Down
  • Should be as simple as swapping for RowNumber with the previous/next entry, i think i should be OK with this.

I suspect something similar to this has already been implemented my someone much more knowledgeable than me. If someone has an example of something similar it would me very much appreciated. Or the bit of script that would go through the subsequent entries in the query results to modify the RowNumber of each one to implement the functions above.
(Or if there is a better way to do what i am trying to do above, i am completely open to suggestions)

As always, any assistance is very much appreciated!

 

[jdraw]

A form is one thing to assist/streamline the user and interface. However, I'm wondering if you have your tables and relationships established and tested with some sample data and scenarios. Tables and relationships are critical to ensuring the form and its various events perform as you expect in your quotation system.

 

[The_Doc_Man]

The "SHIFT EVERY ROW NUMBER" might be either of two queries. You have RowNum X that either you want to INSERT or you want to DELETE.

In each case, I would use an SQL string to build the UPDATE dynamically, substituting for X, Y, or Z as appropriate to my discussion below.

For INSERTING a row:
1. Determine X that you want your NEW ROW to have.
2. Create & Execute SQL for: UPDATE table SET RowNum = RowNum + 1 WHERE RowNum >= X
3. INSERT INTO table (RowNum, etc) VALUES (X, etc.);

For DELETING a row:

1. Determine RowNum X that you want to delete.
2. DELETE * FROM table WHERE RowNum = X
3. UPDATE table SET RowNum = RowNum - 1 WHERE RowNum > X

The SWAP ROWS is actually trickier.

1. Determine RowNum X and RowNum Y that you want to swap. Doesn't matter whether they are adjacent. Determine Z (1 + highest RowNum)
2. UPDATE table SET RowNum = Z WHERE RowNum = X
3. UPDATE table SET RowNum = X WHERE RowNum = Y
4. UPDATE table SET RowNum = Y WHERE RowNum = Z

 

[RogerCooper]

Hello,
I am trying to implement a form that i will use as part of a quotation system. The data displayed will come from a query that filters down by quotation number (selected from a combobox or similar), and orders the results based on a field called RowNumber (I use this field so that the quotation 'rows' are consistently displayed in the same order). The results will be displayed in a subform in datasheet view.
I need to implement a way to be able to select any row (entry) and press separate buttons that do the following functions:

• Add row below
  • Add an entry with the same quotation number (this is OK for me),
  • then set the RowNumber to be the next row number ( also OK),
  • then shift every row number below it so the numbers do not double up (this is the part i am struggling with)
• Delete row
  • Delete current record
  • Re-number the RowNumbers form the consequent entries to compensate for the removed row number
• Move Row Up/Down
  • Should be as simple as swapping for RowNumber with the previous/next entry, i think i should be OK with this.

I suspect something similar to this has already been implemented my someone much more knowledgeable than me. If someone has an example of something similar it would me very much appreciated. Or the bit of script that would go through the subsequent entries in the query results to modify the RowNumber of each one to implement the functions above.
(Or if there is a better way to do what i am trying to do above, i am completely open to suggestions)

As always, any assistance is very much appreciated!

Are you sure defined this correctly? I would expect that quote numbers be assigned sequentially (AutoNumber) and then displayed in a sorted recordset. Why is there a need to renumber anything?

 

[The_Doc_Man]

@RogerCooper, the question makes it clear that dlambert is using this RowNumber field to tightly control display order. The quote numbers are separate based on his description.