I have 2 columns in a continuous form.
One called Balance_1 and the other Balance_2.
I want to transfer the contents of Balance_2 to Balance_1 ... for all
2000 rows in the table.
How to go about this ? Should I use a for/next loop or a SQL
statement.
If you'll recommend a SQL Statement, could you please post some air-
code ? My SQL is v.weak.
Thx.
5  17340 
The code to do this update is:
dim strSql as string
strSql = "update NameOfTable set Balance_1 = Balance_2"
currentdb.Execu te strSql
In fact, you could just use one line of code:
currentdb.Execu te "update NameOfTable set Balance_1 = Balance_2"
So, it is lot less code then writing a udpate loop...
It not clear which records you wanted updated? The above statement will
update all records (rows) in the table. If there is some type of filter, or
restriction, then you have to change the above sql to reflect this (in other
words, the above sql has no relation to the form, or records in a form).
Since this is a sql update statement, they are VERY dangerous. So, since you
new to this...do make a backup before you run/test this type of update...
So....say again:
do make a backup before you do this....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada pl************* ****@msn.com
Thx Albert ! Nice to hear from you after ages. It's me who's been off
this NG for a long time.
I am aware that SQL is far faster than a for/next loop but needed some
hand holding for the statement & syntax.
Secondly, no filter is required. I needed ALL records to be updated.
And yes, I will definitely make a backup of the table before running
this.
Once again ... Thx !!
Best Rgds,
Prakash.
On Aug 6, 1:08 pm, "Albert D. Kallal" <PleaseNOOOsPAM mkal...@msn.com >
wrote:
The code to do this update is:
dim strSql as string
strSql = "update NameOfTable set Balance_1 = Balance_2"
currentdb.Execu te strSql
In fact, you could just use one line of code:
currentdb.Execu te "update NameOfTable set Balance_1 = Balance_2"
So, it is lot less code then writing a udpate loop...
It not clear which records you wanted updated? The above statement will
update all records (rows) in the table. If there is some type of filter, or
restriction, then you have to change the above sql to reflect this (in other
words, the above sql has no relation to the form, or records in a form).
Since this is a sql update statement, they are VERY dangerous. So, since you
new to this...do make a backup before you run/test this type of update...
So....say again:
do make a backup before you do this....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa l...@msn.com
One more small query:
What's the difference between using DoCmd.RunSQL and
currentdb.Execu te. Which is better & Why ? Any pros/cons ??
Rgds,
Prakash.
On Aug 6, 1:08 pm, "Albert D. Kallal" <PleaseNOOOsPAM mkal...@msn.com >
wrote:
The code to do this update is:
dim strSql as string
strSql = "update NameOfTable set Balance_1 = Balance_2"
currentdb.Execu te strSql
In fact, you could just use one line of code:
currentdb.Execu te "update NameOfTable set Balance_1 = Balance_2"
So, it is lot less code then writing a udpate loop...
It not clear which records you wanted updated? The above statement will
update all records (rows) in the table. If there is some type of filter, or
restriction, then you have to change the above sql to reflect this (in other
words, the above sql has no relation to the form, or records in a form).
Since this is a sql update statement, they are VERY dangerous. So, since you
new to this...do make a backup before you run/test this type of update...
So....say again:
do make a backup before you do this....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa l...@msn.com
fp************* *@gmail.com wrote:
>What's the difference between using DoCmd.RunSQL and
currentdb.Exec ute. Which is better & Why ? Any pros/cons ??
Using Currentdb.execu te strsql, dbfailonerrors will give you warning messages.
docmd.runsql ignores such.
Also performance can be significantly different between the two methods. One posting
stated currentdb.execu te took two seconds while docmd.runsql took eight seconds. As
always YMMV.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Noted !! Thank you for the explanation. Makes a lot of sense.
Best Rgds,
Prakash.
On Aug 6, 11:13 pm, "Tony Toews [MVP]" <tto...@teluspl anet.netwrote:
fprakashwadhw.. .@gmail.com wrote:
What's the difference between using DoCmd.RunSQL and
currentdb.Execu te. Which is better & Why ? Any pros/cons ??
Using Currentdb.execu te strsql, dbfailonerrors will give you warning messages.
docmd.runsql ignores such.
Also performance can be significantly different between the two methods. One posting
stated currentdb.execu te took two seconds while docmd.runsql took eight seconds. As
always YMMV.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab. ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: AndrewM |
last post by:
Hello,
I have a SUB which has input variables. If I run it once its okay, but I
need to include it in a for....next loop.
Can this be done ?
for i=0 to 100
call mySub(arr(0,i))
next
|
by: Terry Olsen |
last post by:
How do I loop back to the beginning of a for/next loop before getting to
the end of it? Isn't there an "iterate" command or something like that?
For Each This in That
...code
if This = False Then (start loop over with next This)
...code
Next
*** Sent via Developersdex http://www.developersdex.com ***
|
by: Dave G |
last post by:
I am writing a function to fill in the data in an unbound form.
I have a table with field names of 1, 2, 3 etc up to approx 100. I have
an unbound form with fields called 1, 2 3 etc.
I gave everything names like this because I wanted to simplify the
function which fills the unbound form with data. So:
forms!! = rs!
|
by: BerkshireGuy |
last post by:
I have a routine in Access that loops through an Excel workbook and
executes code base on the worksheet name.
If the worksheet name contains the word 'Stats' than that is a sheet I
want to read (the code).
However, I want to process the company level first because I am going
to need to compare the company stats with those of the individual
agency stats.
|
by: Kevin |
last post by:
ASP.NET 2.0
I have code that updates a database from a number of textboxes on a web
form.
I've had to hard coded references to my web form textboxes. I'd like to
know how I can reference them via a for next loop.
For example, each time I give my command parameters a value I hard coded the
textbox.text reference.
cmd.Parameters.AddWithValue("@Rank", Me.ddlQ1.Text)
| | |
by: semo |
last post by:
Hi everybody,,,
I’m studying VB.Net for the first time so I’m not an expert on it.. that’s why I need your help…
My teacher did not teach us for/next loop.. he just want to know how we can solve the problem.. When I start thinking about the problem I found that I really need to use this statement,,, so could anyone explain it to me…
Another important thing is about how to convert 5 digits like (12345) to extracted and encrypted digits...
|
by: muddasirmunir |
last post by:
i am using vb6
i had make a for next loop which fill data into
the table
now , some time it may needed that we want to
cancel that loop .
now what is the code to exit the for next loop
i want it like that it the user click on the button
the loop will exit
|
by: mygirl22 |
last post by:
1.I have created a moving object (which is a button) in a form (rectangle box) called "Enjoy Button" and it moves when
2. a specific button named "Start Timer" is pressed...When the "Start Timer" button is pressed, the
3. "xTimer" event is called and the button moves in a rectangle box and bounces of the walls...=animation
4. However, i need to write a For..Next Loop in the
5."For Next " Button= a button named for next
6.and also put a...
|
by: smileyc |
last post by:
I have 5 arrays, named array1 array2 array3 array4 array5. They are integer arrays each with 5 elements in them. I want to access all the elements in all the arrays using a for next loop,the pseudo code goes something like this:
for i = 1 to 5 'this loop calls each array
for j = 0 to 4 ' this loop calls each element
if array (i,j) then
' code goes here
end if
next j
next i
|
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: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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.
| |
