473,692 Members | 2,236 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem using update query and Function ResetCounter()

1 New Member
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:

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

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, 370 views)
Dec 20 '09 #1
1 3135
8,834 Recognized Expert Expert
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

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 1001 500.50 INVP 1 1001 500.50 INVPID 1 1002 123.00 INVP 1
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: UPDATE MainInfo SET dAddress='38 Holland Blvd', dCity='miami', dState='FL', dZip='33000', dCountry='USA', dPhone='999987565', dNum='AC15857', dName='Michael A Scott' WHERE did=22'
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 use EXECUTE IMMEDIATE with context.But it is not working. Following is my piece of code. Please help. Thanks in advance. int main() {
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 i change something in php file using in ajax function.it not refreshed,means its shows the previous result it not get updated.i can't understand whats the prob.this is the code i m using: <? include("config.inc.php"); //error_reporting(0); ...
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 one at the bottom of this forum for example but using ajax i created one which updates without you having to refresh the page. It all works fine except it is causing my site to freeze quite often. I have considered removing it but thn i would have...
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 quite helpful about global variables but i eventually ran into difficulty.. the following code is for the click event that calls the Clicker Module Private Sub Image30_Click() Dim counterx As Module Set counterx = Clicker...
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 order, Input field and a submit button. The Category name is being fetched from the oracle db along with the corresponding Category order. In the corresponding input field (text box) the user enters a new category order which gets stored in the...
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 session_start(); if (!isset($_SESSION)) {
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 message as in alert box as " Internet Explorer cannot open the Internet site http://google.citycarrentals.com.au/viewalllocations.php . Operation aborted". It is working in Mozilla . Here i mentioned my code . I am facing this problem several...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.