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