Clear Continuous Subform Fields

[Ben via AccessMonster.com]

I have a continuous subform on my main form. I have a button on my main form
to clear the amount field only on the subform. What happens is that the user
enters amounts, but all the amounts in the subform may be incorrect, so they
would like to hit the clear button to clear out only the amount field on the
continuous subform. There is never more than 6 rows/records in the subform.

Does any one have code to do this? right now i'm using

forms!frmDept!frmProj.form!sAmt = null

but when i click on the clear button, it just clears the first amount not the
other five on the continuous subform.

Please help.

Ben

 

[Stefan Hoffmann]

hi Ben,

Does any one have code to do this? right now i'm using

forms!frmDept!frmProj.form!sAmt = null

but when i click on the clear button, it just clears the first amount not the
other five on the continuous subform.

If your subform datasource is a simple table or query one the use

Dim SQL As String

SQL = "UPDATE TableOrQuery " & _
"SET Fieldname = NULL " & _
"WHERE Parentkey = " & ParentkeyValue

CurrentDb.Execute SQL, dbFailOnError
Subform.Form.Requery

Otherwise use the .Recordset clone method:

With SubForm.Form.RecordsetClone
.MoveFirst
Do While Not .Eof
![Field] = Null
.Update
.MoveNext
Loop
End With

(not tested, maybe you have to use the DAO way, same logic)

mfG
--> stefan <--

 

[Ben via AccessMonster.com]

Hi Stefan,
I tried the code but I receive the following error:

"Update or CancelUpdate without AddNew or Edit"

Any suggestions?

Thanks,
Ben

hi Ben,

Does any one have code to do this? right now i'm using

forms!frmDept!frmProj.form!sAmt = null

but when i click on the clear button, it just clears the first amount not the
other five on the continuous subform.

If your subform datasource is a simple table or query one the use

Dim SQL As String

SQL = "UPDATE TableOrQuery " & _
"SET Fieldname = NULL " & _
"WHERE Parentkey = " & ParentkeyValue

CurrentDb.Execute SQL, dbFailOnError
Subform.Form.Requery

Otherwise use the .Recordset clone method:

With SubForm.Form.RecordsetClone
.MoveFirst
Do While Not .Eof
![Field] = Null
.Update
.MoveNext
Loop
End With

(not tested, maybe you have to use the DAO way, same logic)

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Ben,

Hi Stefan,
I tried the code but I receive the following error:

"Update or CancelUpdate without AddNew or Edit"

Any suggestions?

Use the DAO methods.

With SubForm.Form.RecordsetClone
.MoveFirst
Do While Not .Eof
![Field] = Null
.Update
.MoveNext
Loop
End With

With SubForm.Form.RecordsetClone
.First
Do While Not .Eof
.Edit
![Field] = Null
.Update
.Next
Loop
End With



mfG
--> stefan <--