Results 1 to 6 of 6
  1. #1
    dorhoi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    2

    Updating Each Record in a Continuous Form Until End of Records w/o 2105 error

    Hello! I'm completely new to all these Access forums so please be patient . I like to first thank everyone on these forums as I've had so much success solving many of my problems just by reading through the responses in these forums! However, this problem has been lingering for years and I have not been able to take any of the related responses to completely solve it! Therefore, I'm finally going to put it out for help!



    Basically, what I currently have works! Albeit, I have to accept a "Run-Time Error '2105'; You can't go to the specified record" error message. However, if I simply select "End", my form is updated as I needed! The VBA code I'm using is as simple as it gets:

    Private Sub Form_Current()


    Forms!Form1![Worth] = Nz((DSum("[amount]", "banking deposits at", "[BANK]=FORMS![Form1]![BANK NAME] And [Asset Category]=Forms![form1]![asset Category]"))) - Nz((DSum("[amount]", "banking WITHDRAWL at", "[BANK]=FORMS![Form1]![BANK NAME] And [Asset Category]=Forms![form1]![asset Category]"))) - Nz((DSum("[amount]", "checks query", "[bank] = forms![form1]![bank name] and [check_no] > 0 and [Asset Category]=Forms![form1]![asset Category]")))

    DoCmd.GoToRecord acForm, "Form1", acNext, 1 '



    End Sub


    It's running a complex calculation and populating the field "Worth" in form "Form1" for the 1st record then moves to the next record and so on. As mentioned, I expect the 2105 error as it'll eventually get to the end of records. But, again, it works!

    As mentioned, I've been trying to update the code to eliminate the 2105 error but have had no luck! I'm stuck on the code below! I really believe I'm in the right direction but it just doesn't work! No errors but doesn't update the records as needed! As is, this only updates the 1st record. In a slight variation, nothing gets updated! And in yet another slight variation, it crashes the entire database causing a restart!

    Dim rstForm1 As DAO.Recordset
    Set rstForm1 = Forms!Form1.Form.Recordset

    With rstForm1
    rstForm1.MoveFirst

    Do Until Not rstForm1.EOF '
    rstForm1.Edit

    Forms!Form1![Worth] = Nz((DSum("[amount]", "banking deposits at", "[BANK]=FORMS![Form1]![BANK NAME] And [Asset Category]=Forms![form1]![asset Category]"))) - Nz((DSum("[amount]", "banking WITHDRAWL at", "[BANK]=FORMS![Form1]![BANK NAME] And [Asset Category]=Forms![form1]![asset Category]"))) - Nz((DSum("[amount]", "checks query", "[bank] = forms![form1]![bank name] and [check_no] > 0 and [Asset Category]=Forms![form1]![asset Category]")))

    rstForm1.Update
    rstForm1.MoveNext

    Loop
    End With


    Any help would be greatly appreciated! Please let me know any questions, additional info needed, etc.

    Thanks!
    Hoi




  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If Not rst.EOF Then rstForm1.MoveNext

    Saving aggregate data is usually a bad design.
    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.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I think in you code remove the NOT
    Do Until rstForm1.EOF ' instead of Do Until Not rstForm1.EOF '

    Or can use the If statement like June7 posted.

  4. #4
    dorhoi is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    2
    Wow! So simple!
    Thank you!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I didn't even notice the Not in original code. I don't see how code worked at all with it.
    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.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think you could also use
    Code:
    Do WHILE Not rstForm1.EOF '


    Also, Do not forget to close the record set and destroy the assignment
    Code:
    rstForm1.Update
    rstForm1.MoveNext
    
    Loop
    End With
    
    
    'clean up
    rstForm1.Close
    Set rstForm1 = Nothing

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

Similar Threads

  1. Replies: 4
    Last Post: 09-25-2018, 06:31 AM
  2. Replies: 1
    Last Post: 01-10-2014, 09:51 AM
  3. Replies: 10
    Last Post: 10-22-2013, 07:35 AM
  4. Replies: 5
    Last Post: 08-22-2012, 04:27 AM
  5. Replies: 1
    Last Post: 06-04-2012, 03:37 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