Increment Function in Update Query

Lawrence Salvucci
Lawrence Salvucci used Ask the Experts™
on
I am using the following function to add line #'s in increments of 10 to records in my table. It works fine when I first add line #'s. It starts with 10 as the first line but if I add another record to this table and then update the line #'s it always starts at the next line # after the last one. For example if I have 4 records the initial line #'s are 10, 20, 30, & 40 when I run this function. Then if I add a 5th record and update the table using the same function the lines reset to 50, 60, 70, 80, & 90. How can I get this to reset back to 10 every time I run the function? Any time I add a new record and update it I want it to start over and increment in 10's starting from 10.

Public Function Increment(ivalue As String) As Long

    If Nz(GBL_Category, "zzzzzzzz") = ivalue Then
        GBL_Icount = GBL_Icount + 10
       ' MsgBox icount
    Else
        GBL_Category = ivalue
        GBL_Icount = 10
    End If
    Increment = GBL_Icount
End Function

Open in new window


Here is the SQL view for the Update Query:

UPDATE tblQuoteItemRouter SET tblQuoteItemRouter.OperationNo = Increment([tblQuoteItemRouter].[QuoteItemID])
WHERE (((tblQuoteItemRouter.QuoteItemID)=[Forms]![frmQuoteMain].[Form]![sfrmQuoteItemsBuilder]![txtRecordID]));

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Use Modulus:

GBL_Icount = 10 + (GBL_Icount Mod 50)

Open in new window

Lawrence SalvucciInformation Technology Manager

Author

Commented:
Do I update both instances of "GBL_Icount = " in  my function with your modification? It shows "GBL_Icount = " twice in my function.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The first only.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Lawrence SalvucciInformation Technology Manager

Author

Commented:
Thank you, Gustav. That worked as I wanted it to. Appreciate the quick answer!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!
Lawrence SalvucciInformation Technology Manager

Author

Commented:
Gustav,
It's actually not working as it should. it's resetting every line # after 50. I was using 50 as the last line as an example. I need this to increment for as many records as it finds. Example if there are 6 records then it would be 10, 20, 30, 40, 50, & 60. THen if I rerun that function on the same records then I want it to reset to 10, 20, 30, 40, 50, & 60. The way my code originally was wasn't working. The initial run would be fine but if I added a line and ran the function again it would change line 10 to the next increment in the sequence after the last sequence # that was showing. So if I had 6 records the initial run would be 10, 20, 30, 40, 50, & 60. Then if I added a 7th line it would start line 10 as 70, 80, 90, 100, 110, 120, & 130 instead of resetting them to 10, 20, 30, 40, 50, 60, & 70. Your modification stops at 50 and starts the 6th line at 10. I can't have that.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Oh, I read the 50 as a hardcoded value.
You should study my article:

Sequential Rows in Microsoft Access

You would need something like:

SELECT 10 * RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

Open in new window

Lawrence SalvucciInformation Technology Manager

Author

Commented:
I need this to run using the same function. This runs for any new lines that are tied to our quoting system. So anything the quoteID changes in this table i need to reset the line #'s from 10 and end wherever the last quote ID matches. Then if new records are added for an existing quoteID then it needs to add that line # to the last incremental #.
Lawrence SalvucciInformation Technology Manager

Author

Commented:
And I need this only to be part of my update query. I can't run this from a form or anywhere else. It has to be part of the update query from the way it's designed to add records to this table.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then you probably need to group the numbers by the QuoteId or similar.
The function will do that as well - an example is included.
Lawrence SalvucciInformation Technology Manager

Author

Commented:
But will it do it using just an update query? I cannot use a form for this function. It has to be done in an update query. My function works fine except for how it resets every time you run it on the same records. There has to be an easy way to modify the function without having to change the whole process.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Well, the very first lines of the function reads:

' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.

Open in new window

Lawrence SalvucciInformation Technology Manager

Author

Commented:
And how would I structure it to work using the QuoteID as the trigger to know when to reset it? When the QuoteID changes then it should reset. And if I run the same QuoteID through this function multiple times it should always start at 10. What would I use for the ByVal Key?

RowNumber( ByVal Key As String, Optional ByVal GroupKey As String, Optional ByVal Reset As Boolean) As Long

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The would be the ID of the record (AutoNumber) or some other unique key.

And if I run the same QuoteID through this function multiple times it should always start at 10

I guess not. Then all records would be assigned a 10.
Lawrence SalvucciInformation Technology Manager

Author

Commented:
I tested out your RowNumber function and it does nothing near what I need it to do. I am using it in the same basis as you outlined and it instead of doing an increment for all the QuoteItemID's that are the same it is putting the same RowID for every QuoteItemID that is the same and then changing it for the next QuoteItemID. For example the first QuoteItemID has 4 records and the RowID is returning 594 for all 4 of the records. Then the next QuoteItemID it's returning 604 for all 5 of those records. How is this supposed to increment and where is it coming up with 594 & the 694?? This doesn't seem to work for what I need.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Sounds like you are not providing a unique key for each record. If not, records (in total or within a group) will be assigned identical numbers.

You should download the demo and study the working examples, indeed the query Order Details Row Numbers.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial