Run-Time Error

[prakashwadhwani]

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_Cl ick", the program works
perfectly. However, If I run "Cmd_Compute_Cl ick" 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_Cli ck()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
*************** *************** ************
If IsNull(Me.Txt_Y earEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamation, "Error !"
Me.Txt_YearEndD ate.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_YearEnd Date)))

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

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

End Sub

[prakashwadhwani]

The funny part is that once I click on either "End" or
"Debug" (buttons provided by the error msg) & then again run
"Cmd_Compute_Cl ick", it works fine.

Best Rgds,
Prakash.


prakashwadhw... @gmail.com 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_Cl ick", the program works
perfectly. However, If I run "Cmd_Compute_Cl ick" 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_Cli ck()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
*************** *************** ************
If IsNull(Me.Txt_Y earEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamation, "Error !"
Me.Txt_YearEndD ate.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_YearEnd Date)))

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

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

End Sub

[Scott McDaniel]

On Mon, 30 Jul 2007 03:51:42 -0700, pr************* @gmail.com 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.SomeTextFiel d = 0

Generally, with Bound forms, you should do all calculations on the form (i.e. use Me.SomeField + Me.SomeOtherfie ld, 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_Cl ick", the program works
perfectly. However, If I run "Cmd_Compute_Cl ick" 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_Ba l = Nz(ClBal_Final)
Below are my 2 subs ... hope somebody can throw more light on this.
Private Sub Cmd_Compute_Cli ck()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
************** *************** *************
If IsNull(Me.Txt_Y earEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamatio n, "Error !"
Me.Txt_YearEndD ate.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_YearEnd Date)))

'===== 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@takemeout _infotrakker.co m
www.infotrakker .com

[prakashwadhwani]

Thanks Scott for such a prompt answer.

However, your suggestion: Me.SomeTextFiel d = 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@NoSpam_I nfotrakker.com>
wrote:

On Mon, 30 Jul 2007 03:51:42 -0700, prakashwadhw... @gmail.com 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.SomeTextFiel d = 0

Generally, with Bound forms, you should do all calculations on the form (i.e. use Me.SomeField + Me.SomeOtherfie ld, 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_Cl ick", the program works
perfectly. However, If I run "Cmd_Compute_Cl ick" 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_Cli ck()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
*************** *************** ************
If IsNull(Me.Txt_Y earEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamation, "Error !"
Me.Txt_YearEndD ate.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_YearEnd Date)))

'===== 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@takemeout _infotrakker.co mwww.infotrakke r.com

[prakashwadhwani]

I'm sorry Scott ... I forgot to answer your question.

Yes !! The form is bound ! So once again ... how do I UPDATE [ALL
ROWS] of the the INIT_BAL field without a RunSQL command ??

Thx again & Hope someone can point me in the right direction.

Rgds,
Prakash.


On Jul 30, 3:28 pm, Scott McDaniel <scott@NoSpam_I nfotrakker.com>
wrote:

On Mon, 30 Jul 2007 03:51:42 -0700, prakashwadhw... @gmail.com 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.SomeTextFiel d = 0

Generally, with Bound forms, you should do all calculations on the form (i.e. use Me.SomeField + Me.SomeOtherfie ld, 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_Cl ick", the program works
perfectly. However, If I run "Cmd_Compute_Cl ick" 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_Cli ck()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
*************** *************** ************
If IsNull(Me.Txt_Y earEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamation, "Error !"
Me.Txt_YearEndD ate.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_YearEnd Date)))

'===== 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@takemeout _infotrakker.co mwww.infotrakke r.com

[prakashwadhwani]

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... @gmail.com 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_Cl ick", the program works
perfectly. However, If I run "Cmd_Compute_Cl ick" 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_Cli ck()
Dim rs As Object
Set rs = Me.Recordset
rs.MoveFirst

'**** Performing Validations here
*************** *************** ************
If IsNull(Me.Txt_Y earEndDate) Then
MsgBox "YEAR-ENDING DATE cannot be left BLANK !", vbOKOnly +
vbExclamation, "Error !"
Me.Txt_YearEndD ate.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_YearEnd Date)))

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

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

End Sub