Update Data Loop (1 Viewer)

[teel73]

Here's my problem ... I need to get the key stamp populated for the records that don't have a key stamp. For example if you look at the database I attached and in the table you'll see [keystamp], [field20]. [field20] repeats Driver, Master, Bottom and they should have the same key stamp. But you'll see only the line with the driver has the keystamp populated. I know there is some code I could write but I don't know how I should do it. What I want to do is in that table, loop until the last record. Move to the 1st record, copy the keystamp value, then move to the next record and paste the keystamp value, then move to the next record and paste the keystamp value. Repeat those steps until the end of the file. Can someone please help with this. Hopefully that was clear. Thanks.

 

[rolaaus]

I don't know if I'm understanding completely, but you may just want an "update" query.

Create a new query and select the desired table, then only chose the KeyStamp field, and open the query in dataset mode. Copy the value you want (the first value shown, if I'm understanding), then go back into design mode

In the criteria of KeYStamp field enter Is Null, then goto Query -> Update and where it says "Update to" paste the value that you copied, then you have to make sure to Run the query, and not just display it (click the red exclamation mark)

 

[gemma-the-husky]

get a query sorted in the order you want

then

{you can just put this in a module and run it}
sub fixit

dim rst as recordset
dim dbs as database
dim lastitem as date

set dbs = currentdb
set rst = dbs.openrecordset("myquery")

lastitem = 0
while not rst.eof
     if nz(rst!keystamp,0)<>0 then
          lastitem= rst!keystamp
     else
          if lastitem<>0 then 
              rst.edit
              rst!keystamp=lastitem
              rst.update
         end if
     end if
     rst.movenext
wend
end sub

this will not update any items until there is one with a value to start the process.
take a copy of your table just in case it isnt right

 

[teel73]

thank you gemma-the-husky,

That put me in the right direction. Here's the code I used:

Do Until rst.EOF
    strMasterCut = rst!MasterCut
    rst.MoveNext

        Do While IsNull(rst!MasterCut)
            rst.Edit
            rst!MasterCut = strMasterCut
            rst.Update
            rst.MoveNext

        Loop

Loop