Update and delete recordset in a loop

[herbertioz]

I have a database linked to another and the task is to sum each record from scale1, delete current record (after calc sum) and put it into another table in another database.

How can I ensure that each record sum, record for record and delete it afterwords?

Option Compare Database

Private Sub Form_Timer()
Dim db As Database
Dim Sum As Integer
Sum = 0
   Dim rs As DAO.Recordset
  
   
   Set db = CurrentDb()
   
   
   LSQL = "select weight from scale1"
   
   Set rs = db.OpenRecordset(LSQL)
   
   Do While Not rs.EOF
    
    Sum = Sum + rs("weight")
    
   
    sSql1 = "UPDATE loadings SET loaded_Weight = " & Sum & " WHERE ID = " & Me.ID.Value & ";"
    CurrentDb.Execute (sSql1)
    
       
    rs.MoveNext
    
    Loop
      
     ' Delete weight
    'sSql = "Delete * from scale1 where weight=" & rs("weight")
    'CurrentDb.Execute (sSql)
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
  
End Sub

 

[jzwp22]

I'm not really sure that your code follows what you described in your post, so let's ignore the code at present and just concentrate on what you want to do. Can you describe in detail what you want to have happen?

 

[herbertioz]

Ok, I want to take out data from one table and use a loop to go record by record and do a running sum on all weights. Then I want to copy the results in another table and in the same time, delete the records that I have copied from the source table (to save space).

In the end I will present the sum in a field in a form.

 

[jzwp22]

...use a loop to go record by record and do a running sum on all weights. Then I want to copy the results in another table ...

When you say you want to copy the results, do you mean that you want a new record for each successive sum amount of the running sum added to another table?

For example, lets say that you have the following 3 records in your scale1 table:

ID|weight
1|100
2|144
3|122

So the first time through the loop you want to create a new record with the weight of 100, then the second time through the loop you want to create a second new record with a weight of 244 (100+144). Finally, the third time through the loop, you want to create a third new record with a weight of 366 (100+144+122). Is that what you are after?

 

[herbertioz]

For example in the sourcetable:

id weight
1 1000
2 1000
3 1000

loop thru the records and make a running sum: 1000+1000+1000=3000 and delete record id 1-3 after use.

If the sourcetable has got new weights:

id weight
4 1000
5 1000

Do a running sum again and now the sum in the field = 3000+1000+1000=5000
Delete record id 4-5. Its important to delete the weights after use.
I will use the same record in the destinationtable, not a new record for every weight.

You understand?

 

[jzwp22]

OK, technically speaking you do not need the loop. The code would go something like this (air code, not tested):

dim mySum as long
dim mySQL as string

'check the source table to see if it has new weights

If DCount("*","Scale1")>0 Then

'get the sum
mySum=DSum("weight","Scale1")

'delete the records

mySQL="DELETE * FROM scale1"
currentdb.execute mySQL, dbfailonerror

'Check to see if a record already exists in the destination table; if yes, update the sum, if no create the new record (I assume that the record in the destination table is related to the record on the main form indicated by the value in the control named ID in your original code

If DCount("*","destinationtablename","ID=" & me.ID)>0 THEN

mySQL= "Update destinationtablename SET sumfield=" & mySUM & " WHERE ID=" & me.ID

ELSE

mySQL="Insert into destinationtablename(ID,sumfield) VALUES (" & me.ID & "," & mySum & ")"

END IF

'execute the appropriate query

currentdb.execute mySQL, dbfailonerror

END IF

 

[herbertioz]

Thanks you very much for help.

I found a problem with that solution in testing. When the sourcetable (scale1) gets new weights, then the field which contains the results are not correct.

Some suggestions?

There will be new weights all the time in scale1 table. The code has to be executed with a timer function every 5 second to sum new weights.

 

[jzwp22]

then the field which contains the results are not correct.

Do you mean the sum field in the destination does not contain the correct results?

OK, I think I see the problem. I did not add in the existing value in the sum field in the Update query.

Try this:

mySQL= "Update destinationtablename SET sumfield=sumfield+" & mySUM & " WHERE ID=" & me.ID