469,326 Members | 1,423 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Excel-Type Update

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 1244
Mihail
759 512MB
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,321 Expert Mod 2GB
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
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
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 512MB
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,321 Expert Mod 2GB
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

Post your reply

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

Similar topics

6 posts views Thread by David Shorthouse | last post: by
10 posts views Thread by Randy Harris | last post: by
7 posts views Thread by Mark Carlyle via AccessMonster.com | last post: by
5 posts views Thread by colleen1980 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.