473,842 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Excel-Type Update

24 New Member
I have a table with two columns. The first being an auto number field. The second column contains a predefined text only in a few records. I would like to run an update query using VBA to update all the blank records with the predefined text but only for those records that appear below this text. On reaching the new text all records under this text will be updated with the new text until the next new text is reached. Can someone assist?
Mar 2 '12 #1
6 1366
Mihail
759 Contributor
I don't think that this can be done using update query.
You easy can do that using VBA.
Mar 2 '12 #2
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
Im not that sharp on SQL, but can't immediatly think of a way to do it using SQL. Its however fairly easy to do in VBA.

First however, your approach seems to be breaking some normalization rules, but I can't be sure based on the limited info givin here. Please read http://bytes.com/topic/access/insigh...ble-structures and ensure that your following the guidelines laid out there. Incorrect table design can easily triple (or worse) your workload!


Also understand that a row in a table by default do not really have a definition of below/above.In a query, in which you have applied a sort by using ORDER BY, you can start talking about the order of the rows. Since you have an autonumber field, we can use that to apply order.

This code should work as a guide for you. In my example I have simlpy named the field txExample. It assumes that for the first record in the table that txExample is NOT empty.
Expand|Select|Wrap|Line Numbers
  1. Public sub CopyInformation()
  2.   'Start Variables
  3.     Dim DB as Dao.Database
  4.     Dim rs as Dao.RecordSet
  5.     Dim strText as string
  6.  
  7.   'Open db and recordset  
  8.     set DB=CurrentDB()
  9.     set rs=DB.OpenRecordset("SELECT [txExample] from tblExample ORDER BY [KEY_Autonumber]",dbopendynaset)
  10.  
  11.   'Loop through records, updating if necessary
  12.     Do While Not rs.EOF
  13.       'Check if value exists
  14.         If rs![txExample] & "" <> "" Then
  15.           'Field is not empty
  16.           strText=rs!TxExample
  17.         else
  18.           'Field is empty, update with text
  19.           rs.Edit
  20.             rs!txExample=strText
  21.           rs.Update
  22.         End If
  23.     rs.MoveNext
  24.   Next
  25.  
  26.   'Cleanup
  27.     rs.close
  28.     set rs=nothing
  29.     set DB=nothing
  30. End Sub
Mar 2 '12 #3
Taaner
16 New Member
One extra thing on the solution of TheSmileyCoder: if the first record(s) is empty, another empty string will been written to it. If you want to avoid this, fill strText with a default value before the loop.
Mar 2 '12 #4
Vish Narayan
24 New Member
SmileyCoder
My table is something like this:
Autonumber TextDescription
1
2 TEXTAAAAAAAA
3
4
5 TXTDDDDDDDDD
6
7
Required that 3 and 4 get updated with TEXTAAAAAAAA , 6 and 7 with TXTDDDDDDDDDDDD

I get VBA error in SmileyCoder's solution "Next without For".
Mar 3 '12 #5
Mihail
759 Contributor
Replace in line 24 (Smiley's code) , Next to Loop .
Also your case is as Taaner (post #4) anticipate.
Mar 3 '12 #6
TheSmileyCoder
2,322 Recognized Expert Moderator Top Contributor
When you ask for code for a very specific problem, you will sometimes receive "air" code. I don't really know why its referred to as air code, but the meaning is that air code is not tested. Its code I wrote directly in the browser window, and I haven't even checked to see if the code would compile. I dont really have any test data to test it on, so I write the code the best I can. What you need to understand is that simply copying code of the net without trying to understand what the code does, and why it does it, will eventually lead to trouble on your behalf. Mihail for example looks at the code, and tries to understand it, and quickly spots that I accidentally used Next instead of Loop.

Here is revised code, taking into account the possibility that the first X rows could be empty.

Expand|Select|Wrap|Line Numbers
  1.     Public sub CopyInformation()
  2.       'Start Variables
  3.         Dim DB as Dao.Database
  4.         Dim rs as Dao.RecordSet
  5.         Dim strText as string
  6.  
  7.       'Open db and recordset  
  8.         set DB=CurrentDB()
  9.         set rs=DB.OpenRecordset("SELECT [txExample] from tblExample ORDER BY [KEY_Autonumber]",dbopendynaset)
  10.  
  11.       'Loop through records, updating if necessary
  12.         Do While Not rs.EOF
  13.           'Check if value exists
  14.             If rs![txExample] & "" <> "" Then
  15.               'Field is not empty
  16.                 strText=rs!TxExample
  17.             else
  18.               'Field is empty, update with text
  19.                 'Check to see if strText has been set yet
  20.                   If strText<>"" then
  21.                     rs.Edit
  22.                       rs!txExample=strText
  23.                     rs.Update
  24.                   End If
  25.             End If
  26.         rs.MoveNext
  27.       Loop
  28.  
  29.       'Cleanup
  30.         rs.close
  31.         set rs=nothing
  32.         set DB=nothing
  33.     End Sub
  34.  
  35.  
Mar 3 '12 #7

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

Similar topics

6
2227
by: David Shorthouse | last post by:
Hello folks, I have a problem with an update query on an asp not updating the table in an Access db. The code runs, I have no errors, but when I examine the table, nothing was updated. The query works as it should in Access. Could anyone direct me to some sources that describe solutions to what is likely a common problem? The strange thing is, I have another update query for a different db on an asp that works just great. Is there...
3
7057
by: Bill Clark | last post by:
I have about 20,000 records pulled from Excel that I need to update. What I need to do is run an update query that bascially says: If a field is null, update it with the previous record value of that same field. In some instances, it will have to go back a few records before it finds a value that is not null. Can this be done? Thanks Bill
5
13234
by: Don Seckler | last post by:
I have an update query that runs when a report closes. I have several reports that will need to run the update query with diferent criteria. I'd like to simply make the criteria change in the report vba instead of making different queries. Here's my query sql: UPDATE Draw SET Draw.Billed = Yes WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
10
3292
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This wouldn't work (nothing changed): UPDATE tblManuals SET tblManuals.PARTNUM = Trim(); Would someone please tell me how to do an update query that will trim the spaces?
5
4706
by: Andrew | last post by:
I've got a list box that selects a record on a subform, and in that subform are a few text fiels and a button that runs an update query. How do I have the update query button run and only update the record that is selected in the list box? The data updates right, but I can't get the update query to do anything but update all of the records. Thanks, Andrew
4
11347
by: deko | last post by:
I'm trying to update the address record of an existing record in my mdb with values from another existing record in the same table. In pseudo code it might look like this: UPDATE tblAddress SET AddressDescription of Entity 456 = AddressDescription of Entity_ID 123 Address1 of Entity 456 = Address1 of Entity_ID 123 City of Entity 456 = City of Entity_ID 123
7
3546
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" Transactions = name of the table I want to update balance = name of the field i want to update daily balance= name of the query result that I want to move to the table
2
5075
by: bobabooey2k | last post by:
I have an update query with one field having in its "Update to" cell a DLookup statement. This query takes 2-3 minutes on 3000 records. Can I avoid dlookup here using multiple queries? An underlying subquery to this Update query involves a MAX function on a date field, which is then used in the DLookup statement. Any help appreciated. Thanks Richard
5
3005
by: colleen1980 | last post by:
Hi: In my table there is a field of type checkbox. I create a button on my form and wants to deselect all the checkboxes in that field (PrintQueue). Table: Research_New PrintQueue Format Yes/No Thanks for help.
1
3150
by: giovannino | last post by:
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...
0
9709
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10939
Oralloy
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...
1
10669
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,...
0
10308
tracyyun
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...
0
9449
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5882
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4498
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
2
4086
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3140
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.