Remove ControlSource from All Controls on a Form

[bduncan]

Hi,

I have a simple loop that is supposed to loop through all of the text
and combo box controls on a form and capture the value of the control
in a temp variable (strTemp), clear the ControlSource property and then
set the value of the control to the value of the temp variable,
strTemp.

For i = 0 To Forms("Form1").Controls.Count - 1

If Forms("Form1").Controls(i).ControlType <> acCommandButton And
Forms("Form1").Controls(i).ControlType <> acLabel And
Forms("Form1").Controls(i).ControlType <> acSubform Then

strTemp = Switch(IsNull(Forms("Form1").Controls(i).Value), "", Not
IsNull(Forms("Form1").Controls(i).Value),
Forms("Form1").Controls(i).Value)
Forms("Form1").Controls(i).ControlSource = ""
Forms("Form1").Controls(i).Value = strTemp

i = i + 1

End If

Next

DoCmd.Restore

The ControlSource does not retain the value of "". I don't receive any
error messages. When I use MsgBox to test the values of the
ControlSource properties for the controls as the code is executing, it
shows that the value has been set to "", but when the form is restored,
the ControlSource values have not been set to "". The form is in
Preview view when this code is executing because I get an error saying
that the Value property cannot be modified when I try it in Design
view. I tried reverting to Design view just for the
Forms("Form1").Controls(i).ControlSource = "" line of code, but this
does not solve the problem. Any ideas on what I am doing wrong?

Thanks,

Brenna

 

[Allen Browne]

Suggestions:

1. The changes will stick only if you do this in design view. If you do this
in Form view (or Continuous or Datasheet view), the changes will be lost
when the form is closed.

2. Rename the controls. Access gets confused if the controls have the same
Name as a field, but are not bound to the field.

3. What are you doing here? It's fairly easy to crash Access when
reassigning the Recordset/Fields beyond just filtering or sorting. For
example, if a field disappears or changes data type, Access may not cope.