Increment Function in Update Query

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

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Use Modulus:

GBL_Icount = 10 + (GBL_Icount Mod 50)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Do I update both instances of "GBL_Icount = " in  my function with your modification? It shows "GBL_Icount = " twice in my function.
0
Gustav BrockCIOCommented:
The first only.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you, Gustav. That worked as I wanted it to. Appreciate the quick answer!
0
Gustav BrockCIOCommented:
You are welcome!
0
Lawrence SalvucciInformation Technology ManagerAuthor 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.
0
Gustav BrockCIOCommented:
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

0
Lawrence SalvucciInformation Technology ManagerAuthor 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 #.
0
Lawrence SalvucciInformation Technology ManagerAuthor 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.
0
Gustav BrockCIOCommented:
Then you probably need to group the numbers by the QuoteId or similar.
The function will do that as well - an example is included.
0
Lawrence SalvucciInformation Technology ManagerAuthor 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.
0
Gustav BrockCIOCommented:
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

0
Lawrence SalvucciInformation Technology ManagerAuthor 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

0
Gustav BrockCIOCommented:
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.
0
Lawrence SalvucciInformation Technology ManagerAuthor 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.
0
Gustav BrockCIOCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.