As per this thread, the SQL Request control does not retain values entered in NewForm.aspx when returned to Edit mode and Execute at runtime is set to yes.
My scenario is that I have SQL Request control set to Checkbox Multi Select. A user creates a new item and will selects items from the SQL Request control and submit. A user will return to edit the item and realistically will want to deselect existing options and select new options. The SQL Request control will not retain these marked options when the form is opened in edit mode, if the option to "Execute in Edit" is set to Yes, but it will execute the stored procedure in the form and list the returned values. If "Execute in Edit" is set to No the values will be retained but the stored procedure is not executed and the user will not have any new options to select.
Now, the items being selected are being written to a Child list so I have them available that way to list, but I can't do anything with them in the original form that way, and it is likely the user will want to deselect the items originally selected when the form was created.
I'm looking for suggestions on how to overcome this issue with the SQL Request control. My thoughts are along the lines of multiple SQL request controls, one which works in New mode and then doesn't execute again, and another that is hidden in New mode and executes in Edit mode, then I'll have to use the workflow to delete all the child items everytime the form is edited, and then recreate the child items and any new items selected to be sure that any deselected items no longer exist in the child list.
Does this make any sense to anyone and if so, can you think of a better way of doing it, because this looks ugly to me.