Excel-Type Update

[Vish Narayan]

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?

[Mihail]

I don't think that this can be done using update query.
You easy can do that using VBA.

[TheSmileyCoder]

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.

Public sub CopyInformation()
  'Start Variables
    Dim DB as Dao.Database
    Dim rs as Dao.RecordSet
    Dim strText as string
 
  'Open db and recordset  
    set DB=CurrentDB()
    set rs=DB.OpenRecordset("SELECT [txExample] from tblExample ORDER BY [KEY_Autonumber]",dbopendynaset)

  'Loop through records, updating if necessary
    Do While Not rs.EOF
      'Check if value exists
        If rs![txExample] & "" <> "" Then
          'Field is not empty
          strText=rs!TxExample
        else
          'Field is empty, update with text
          rs.Edit
            rs!txExample=strText
          rs.Update
        End If
    rs.MoveNext
  Next

  'Cleanup
    rs.close
    set rs=nothing
    set DB=nothing
End Sub

[Taaner]

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.

[Vish Narayan]

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".

[Mihail]

Replace in line 24 (Smiley's code) , Next to Loop .
Also your case is as Taaner (post #4) anticipate.

[TheSmileyCoder]

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.

    Public sub CopyInformation()
      'Start Variables
        Dim DB as Dao.Database
        Dim rs as Dao.RecordSet
        Dim strText as string
     
      'Open db and recordset  
        set DB=CurrentDB()
        set rs=DB.OpenRecordset("SELECT [txExample] from tblExample ORDER BY [KEY_Autonumber]",dbopendynaset)
     
      'Loop through records, updating if necessary
        Do While Not rs.EOF
          'Check if value exists
            If rs![txExample] & "" <> "" Then
              'Field is not empty
                strText=rs!TxExample
            else
              'Field is empty, update with text
                'Check to see if strText has been set yet
                  If strText<>"" then
                    rs.Edit
                      rs!txExample=strText
                    rs.Update
                  End If
            End If
        rs.MoveNext
      Loop
     
      'Cleanup
        rs.close
        set rs=nothing
        set DB=nothing
    End Sub