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

dlambert

Member
Local time
Today, 13:15
Joined
Apr 16, 2020
Messages
37
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

Nifty Access Guy
Staff member
Local time
Today, 12:15
Joined
Jul 9, 2003
Messages
13,221
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:-

 

jdraw

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Jan 23, 2006
Messages
13,760
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

Immoderate Moderator
Staff member
Local time
Today, 06:15
Joined
Feb 28, 2001
Messages
20,224
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

Nifty Access Guy
Staff member
Local time
Today, 12:15
Joined
Jul 9, 2003
Messages
13,221
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:-


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

Registered User.
Local time
Today, 04:15
Joined
Jul 30, 2014
Messages
191
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

Immoderate Moderator
Staff member
Local time
Today, 06:15
Joined
Feb 28, 2001
Messages
20,224
@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

Super Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 19, 2002
Messages
32,221
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?
 

Users who are viewing this thread

Top Bottom