Sequentially modify entries in subsequent query results. (1 Viewer)

[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!

 

[Uncle Gizmo]

I suspect something similar to this has already been implemented

I don't know of anything that does everything you want in one unified solution, but I'm sure you will be able to find little bits and pieces of code and put it together yourself.

I have this code which adds a new similar row, might provide some inspiration:-

Subform Record Duplicator - Nifty Access

Subform Record Duplicator Nifty Access - "Subform Record Replicator" allows you to provide your user with the facility to quickly add sets of duplicate records in a subform. All they need do is specify the number of Records they want to add, select a record to copy, and hey presto you've got a...

www.niftyaccess.com

 

[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

 

[Uncle Gizmo]

Move Row Up/Down

It just occurred to me that one of the features of my "Nifty Switchboard Builder" is the option to move Switchboard items up and down the list. You can see what I mean in the video here:-

Using the Nifty Switchboard Builder - Nifty Access​

More information about the "Nifty Switchboard Builder" on my Nifty Access website here:-

Nifty Switchboard Builder - Nifty Access

Nifty Switchboard Builder One of the best add-in features of Microsoft Access is the Switchboard. It's been around as long as I can remember! However it's a bitch to use, the interface is just well, bad... I have used the Switchboard many times, but I avoid the interface, I edit directly in the...

www.niftyaccess.com

If you are interested in seeing how the code works then drop me a line and I will give you instructions on how you can download a free copy.

 

[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.

 

[Pat Hartman]

In a relational table, rows do not have a relationship to other rows. If you want rows to be ordered, you sort on something. You are superimposing a flat-file process that is visual. You might want to step back and think about what you are trying to do rather than focusing on implementing it visually.

Are you actually trying to insert one quote between two other quotes? Why does the quote need to go there? Would sorting on some other field solve the problem?