Results 1 to 4 of 4
  1. #1
    Muntasser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Posts
    2

    Need your help in a loop in the recordset

    Hi guys

    I have a table called "main" contains five fields "account", "x" , "y" , "netaccount" ,and "notes"
    the field "netaccount" is a calculated field where netaccount=account-x+y
    when I add a new record account field automatically takes the value of netaccount of the previous record


    THE PROBLEM is when I delete any record I need a code that recalculate the account and solve the gap that occurred due to deleting the record
    this image might help you to understand the problem when you try to delete any record

    Click image for larger version. 

Name:	29101671_10215442057340978_7803202377007235072_n.jpg 
Views:	11 
Size:	149.2 KB 
ID:	33164

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    After editing or deleting, use Me.Recalc
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Muntasser is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Posts
    2
    Could you give me more explanation
    Actually I created a Delete button include the following code which recalculate and update account field in entire table after deleting a record


    Private Sub delete_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim y As Long




    Set db = CurrentDb()
    Set rs = db.OpenRecordset("main")


    RunCommand acCmdDeleteRecord
    rs.MoveFirst
    Do While Not rs.EOF
    y=me.netaccount
    rs.movenext
    me.account=y
    Loop
    rs.Close
    Set rs = Nothing
    End Sub


    note: adding rs.edit , rs.update didn't solve the problem

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    This is why saving calculated data is usually a bad design. Data gets 'out of sync'. Do this calculation when needed. Use RunningSum property of textbox on report. The trick may be calculating a starting balance (one discussion https://groups.google.com/forum/#!to...ts/CxhEfuFM7gM) or pulling value from previous record (http://allenbrowne.com/subquery-01.html#AnotherRecord).

    I was able to reproduce the report with just calculations in textboxes. Set NetAccount textbox RunningSum property to OverAll.
    Attachment 33172
    Attached Thumbnails Attached Thumbnails Capture.PNG  
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Recordset loop not updating and breaks
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 02-24-2014, 05:33 PM
  2. Loop Not Progressing Through Recordset
    By nmlinac in forum Programming
    Replies: 1
    Last Post: 02-15-2013, 10:54 AM
  3. Double loop recordset
    By silverspr in forum Programming
    Replies: 7
    Last Post: 03-07-2012, 01:43 PM
  4. Loop through recordset
    By eliedebs in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 12:26 AM
  5. Bulk Email / Loop through recordset
    By smikkelsen in forum Forms
    Replies: 4
    Last Post: 07-12-2010, 06:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums