Re: Run-Time Error

Thanks Scott for such a prompt answer.

However, your suggestion: Me.SomeTextField = 0 will work only for
the current row
& not for all the rows in the table
as I am doing with: DoCmd.RunSQL "UPDATE DPATMST SET Init_Bal = 0;"

Please excuse my ignorance & would greatly appreciate any further
ideas / tips / pointers.

Best Rgds,
Prakash.




On Jul 30, 3:28 pm, Scott McDaniel <scott@xxxxxxxxxxxxxxxxxxxxxx>
wrote:
On Mon, 30 Jul 2007 03:51:42 -0700, prakashwadhw...@xxxxxxxxx wrote:
I have a button on my (continuous) form which when pressed performs
the foll 2 actions:

Is your form Bound? If so, are the UPDATE statements running agains the SAME table/datasource as the Form? If that's
true, then you need to perform the updates on the form ... in other words, instead of using RunSQL, just set the value
directly on the form:

DoCmd.RunSQL "UPDATE DPATMST SET Init_Bal = 0;"

becomes

Me.SomeTextField = 0

Generally, with Bound forms, you should do all calculations on the form (i.e. use Me.SomeField + Me.SomeOtherfield, and
not UPDATE statements, etc) and NOT rely on recordsets/sql statements - assuming, that is, that you're manipulating the
same data. If your ComputeTotals function takes values from your form and uses values from other tables, then that's
perfectly fine (and that's what it appears to do).





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

Scott McDaniel
scott@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx


.

Quantcast