giovannino
Technical User
Dear all,
I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !).
1) Given that I'm not a programmer I can't understand why numbering doesn't start always, running more times the update query, from the same starting parameter assigned (1000000000).
It seems to me that it takes memory last number calculated and running prox update it starts from last table row updated. I need to start always with 100000000 just to feed a 10 char field sequence reference. Is it possible to reset .... it to have this behavior each time I run the upt query ?
2) The update query has only 1 column of table with the update to set to:
GetNextCounter([NR_SEQUENZA])
The other colum is populated by : ResetCounter().
When you save and close it you loose the ResetCounter() column becouse no parameters are defined and so I can't run the query within a macro or from a button. That's a big problem becouse the query is within a weekly basis application and the end user can't go to edit the query and insert manually ResetCounter(). Is there a trick or a workaround ?? (I hope easy one...) to save and run the query each time with no edit it before ?
Any suggestion will be very appreciated.
Giovanni
Option Explicit
Dim mlngCounter As Long
Function ResetCounter()
mlngCounter = 1000000000
End Function
Function GetNextCounter(pvar As Variant) As Long
mlngCounter = mlngCounter + 1
GetNextCounter = mlngCounter
End Function
In the Query:
Select [feilds...], ResetCounter(), GetNextCounter([a valid column]) From table....
How this works:
ResetCounter() takes no parameters so Access (being as efficient as ever
only calls the function once before returning any rows as it thinks the return value will never change, hence the counter only gets reset once.
GetNextCounter() must be sent a valid column as a parameter, Access will realise that it must call this function for every row as each value sent can be different, so it increases the counter on each row.
Please note: This won't work very well for queries & forms where you intend to update data or scroll up and down as the calculated fields gets calculated again and the sequence number will keep going up and up.
I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !).
1) Given that I'm not a programmer I can't understand why numbering doesn't start always, running more times the update query, from the same starting parameter assigned (1000000000).
It seems to me that it takes memory last number calculated and running prox update it starts from last table row updated. I need to start always with 100000000 just to feed a 10 char field sequence reference. Is it possible to reset .... it to have this behavior each time I run the upt query ?
2) The update query has only 1 column of table with the update to set to:
GetNextCounter([NR_SEQUENZA])
The other colum is populated by : ResetCounter().
When you save and close it you loose the ResetCounter() column becouse no parameters are defined and so I can't run the query within a macro or from a button. That's a big problem becouse the query is within a weekly basis application and the end user can't go to edit the query and insert manually ResetCounter(). Is there a trick or a workaround ?? (I hope easy one...) to save and run the query each time with no edit it before ?
Any suggestion will be very appreciated.
Giovanni
Option Explicit
Dim mlngCounter As Long
Function ResetCounter()
mlngCounter = 1000000000
End Function
Function GetNextCounter(pvar As Variant) As Long
mlngCounter = mlngCounter + 1
GetNextCounter = mlngCounter
End Function
In the Query:
Select [feilds...], ResetCounter(), GetNextCounter([a valid column]) From table....
How this works:
ResetCounter() takes no parameters so Access (being as efficient as ever
only calls the function once before returning any rows as it thinks the return value will never change, hence the counter only gets reset once.GetNextCounter() must be sent a valid column as a parameter, Access will realise that it must call this function for every row as each value sent can be different, so it increases the counter on each row.
Please note: This won't work very well for queries & forms where you intend to update data or scroll up and down as the calculated fields gets calculated again and the sequence number will keep going up and up.