asked on

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

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lawrence Salvucci

ASKER

Do I update both instances of "GBL_Icount = " in my function with your modification? It shows "GBL_Icount = " twice in my function.

Gustav Brock

The first only.

Lawrence Salvucci

ASKER

Thank you, Gustav. That worked as I wanted it to. Appreciate the quick answer!

Gustav Brock

You are welcome!

Lawrence Salvucci

ASKER

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.

Gustav Brock

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 Salvucci

ASKER

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 Salvucci

ASKER

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.

Gustav Brock

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 Salvucci

ASKER

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.

Gustav Brock

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 Salvucci

ASKER

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

Gustav Brock

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 Salvucci

ASKER

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.

Gustav Brock

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.