Null all of one control in subform with button

[gangel]

WOW so i tried for 3 hours to do this and failed miserably

- I have a form [Main] with a subform [sub]

[sub] has a control (textbox) called [rcvd]

It is a continuous subform.

I want the user to be able to put all values in then if they want to just cancel and clear it pretty much runs

me.[sub].[rcvd] = ""

I know the above doesnt actually work but i have tried:


All kinds of referencing like above,
creating a public function in [sub] that DOES clear the control ( me.[rcvd] = "")

im guessing i need an update query?? I just thought my idea was sound then it just didnt work lol

[ranman256]

use a query...put in all values to the sub table with an append (using the master ID)
or delete all values with a delete query (using the master ID)

the me.[sub].[rcvd] = ?? will ONLY work on 1 record.

[knarfreppep]

me.[sub].[rcvd] = ?? will ONLY work on 1 record.

And I think that one record will be left in 'being edited' mode.

Try -
DoCmd.RunSQL "UPDATE tblChildren SET rcvd = Null WHERE lngParentID = " & Main.txtID (if your IDs are, as they should be, numbers)

OR

DoCmd.RunSQL "UPDATE tblChildren SET rcvd = Null WHERE strParentID = """ & Main.txtID & """" (if your IDs are, as they should NOT be, strings)

[June7]

DoCmd.RunSQL will trigger warning messages. Options:

DoCmd.SetWarnings = False
DoCmd.RunSQL ...
DoCmd.SetWarnings = True

or

CurrentDb.Execute "UPDATE ..."

In either case, then refresh the form

Me.Sub.Refresh

However, as knarfreppep notes, if editing was initiated and that record is still in edit mode, after the SQL runs will possibly generate an 'edited by another user' error when that record attempts to commit. Either first commit the record or undo the edits.

'Sub' is not a very informative name.