Re: Run-Time Error

Got it at last !! After the UPDATE statement, I changed the
Me.Refresh to Me.Requery & it now works perfectly !!

I presume it puts the recordset back in sync.

Thx to all who helped. (I was really getting a bit desperate - had to
submit tomm to a client).

Best Rgds,
Prakash.





On Jul 30, 2:51 pm, prakashwadhw...@xxxxxxxxx wrote:
I have a button on my (continuous) form which when pressed performs
the foll 2 actions:

1) it resets the field "Init_Bal" to 0
2) it computes the closing balances & stores them in the field
"Init_Bal"

Now ... the 1st time I run "Cmd_Compute_Click", the program works
perfectly. However, If I run "Cmd_Compute_Click" again immediately, I
get the foll error:

Run-time error '-2147352567 (800200009)':
The data has been changed

Access then highlighs the foll (last) line:
Me.Txt_Init_Bal = Nz(ClBal_Final)

Below are my 2 subs ... hope somebody can throw more light on this.

Private Sub Cmd_Compute_Click()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
******************************************
If IsNull(Me.Txt_YearEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamation, "Error !"
Me.Txt_YearEndDate.SetFocus
Exit Sub
End If

'*****************************************************************************

DoCmd.RunSQL "UPDATE DPATMST SET Init_Bal = 0;"
Me.Refresh 'Otherwise if we stop execution here, the Init-Bal
column still reflects the OLD values

rs.MoveFirst
Do Until rs.EOF
Call Compute_Totals
rs.MoveNext
Loop
rs.MoveFirst
rs.Requery
Me.Requery

MsgBox "Closing Balances Computed !", vbOKOnly + vbInformation,
"Message"
End Sub

Private Sub Compute_Totals()
Dim OPBAL_Final As Double, DrAndCr As Double, ClBal_Final As
Double

' ***** Op.Bal from Master-File ... DPATMST *****
OPBAL_Final = Nz(Me.OPBAL)

'===== LEDGERBAL i.e. DEBITS+CREDITS fm DPATDAT ... Calculation
DSUM Way =========
DrAndCr = Nz(DSum("nz([DEBIT])-nz([CREDIT])", "DPATDAT", "[Code] =
" & Me.CODE & " AND [Inv_Date] < " & DMY(Txt_YearEndDate)))

'===== CLOSING BAL Calculation ===============
ClBal_Final = Nz(OPBAL_Final) + Nz(DrAndCr)

' ***** Storing Closing Balances *****
Me.Txt_Init_Bal = Nz(ClBal_Final)

End Sub


.

Quantcast