473,320 Members | 1,933 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem using update query and Function ResetCounter()

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.
Thanks in advance
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.
Attached Images
File Type: jpg upd_query.jpg (16.1 KB, 355 views)
Dec 20 '09 #1
1 3089
ADezii
8,834 Expert 8TB
I am very confused as to the nature of your question, but it seems as though you are attempting to Increment a Counter at the same time that you are Resetting it. Assuming Table1 consists of a single Field named [NR_SEQUENZA], which contains a single Value to be used as a starting point to be incremented; you can change this Value (Reset), then Run your Query by executing the single line of code below:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute "UPDATE Table1 SET [NR_SEQUENZA] = 100000000;", dbFailOnError
Dec 21 '09 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: Patrick Fisher | last post by:
Hi I have a table which Contains entries with RefCode field containing INVP or INVPD Common fields in each entry would be InvoiceNo, Total and PurTyp for example. You could have ...
3
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows:...
2
by: chets | last post by:
Hi All, I am facing problem in executing one dynamic query in PRO *C program on linux. I want to update table mytable by data MADURAI for a column mycolumn1 where primary key is myPK.I want to...
2
by: shivendravikramsingh | last post by:
hi friends, i m using a ajax function for retrieving some values from a database table,and display the values in required field,my prob is that the ajax function i m using is working f9 once,but if...
1
by: staticfire | last post by:
Hi i am in need of help with an ajax related problem on my site. I've coded an active users list which shows the members and guests online. I'm sure you've all seen active user lists before, like the...
2
by: panteraboy | last post by:
Hello again Humble helpfulls. Im having difficult with a module i have created. As its my first it was never going to be straightforward lol. But I was looking at one of the other treads and found it...
4
by: raghuvendra | last post by:
Hi I have a jsp page with 4 columns: namely Category name , Category order, Input field and a submit button. All these are aligned in a row. And Each Category Name has its corresponding Category...
2
coolv
by: coolv | last post by:
Hello I have problem in my page that the dropdown box is not displaying data according to selection of first dropdown.Please help me. Below is my code. thanks.............. <?php ...
2
by: swethak | last post by:
Hi, I am getting the problem the problem with google map in Internet Explorer. This map worked fine in mozilla . When i opened the same map in Internet Explorer i am getting the error...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.