Clearing ControlSource Slows Down Code BigTime!

[chunter33]

Hey there, I had a form in access 2007. I had recently upgraded it from 97 where it ran within seconds. Now when I run it it takes something like 20-30 seconds. I pinpointed one of the areas where code was running really slow.

'Unbinds all text and combo boxes from the work form
Sub clearform()
Dim o As Object
Open MyFile For Output As fnum

 For Each o In Me.Form
  If Left(o.Name, 4) = "Text" Then o.ControlSource = ""
  If Left(o.Name, 5) = "Combo" Then o.ControlSource = "" Next o
End Sub

There's a number of controls on the form, but that isn't the issue from what I can tell. Actually looping through each of the controls is a very quick task. However, every combo box and text control has a control source property set. And there's 32 of them. When the code that sets the controlsource to "" gets run it takes about a second or two. You do the math.

Is there any way for me to speed up this task? They each have a different control source.

 

[TheAceMan1]

How are ya New Postchunter33 . . .

In the [blue]Tag[/blue] property of each control of interest put a question mark [purple]?[/purple] ([red]no quotations please[/red]). Then try the following code:

[blue]   Dim ctl As Control
   
   For Each ctl In Me.Controls
      If ctl.Tag = "[purple][b]?[/b][/purple]" Then ctl.ControlSource = Null
   Next[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Andrzejek]

In this line:
[tt]For Each o In Me.Form[/tt]
isn't Me the Form itself?

And to expend on TheAceMan1's code I would try:

Dim ctl As Control      

For Each ctl In Me.Controls
    If TypeOf ctl Is TextBox Or _
    TypeOf ctl Is ComboBox Then
        ctl.ControlSource = ""
    End If
Next ctl

Have fun.

---- Andy

 

[MajP]

I would do it as AceMan suggests (except I think you have to set a control source to "" not null). However, I have to think there is something else going on. I wanted to see if there would be a big difference and I assumed there would. I put a few hundred controls on a form. The combos and textboxes had a ? in the tag.

Private Sub cmdSpeed_Click()
  Dim ctrl As Access.Control
  Dim oCtrl As Object
  Dim t As Long
  'using control object and tag property
  t = Timer
  For Each ctrl In Me.Controls
    If ctrl.Tag = "?" Then ctrl.ControlSource = ""
  Next ctrl
  Debug.Print "duration using ctrl and tag " & Timer - t
  'using control object and type of  
  t = Timer
  For Each ctrl In Me.Controls
    If TypeOf ctrl Is Access.TextBox Or TypeOf ctrl Is Access.ComboBox Then ctrl.ControlSource = ""
  Next ctrl
  Debug.Print "duration using ctrl and typeof " & Timer - t
  'original using generic object and text comparison
  t = Timer
  For Each oCtrl In Me.Form
    If Left(oCtrl.Name, 4) = "text" Or Left(oCtrl.Name, 4) = "combo" Then oCtrl.ControlSource = ""
  Next oCtrl
  Debug.Print "duration using object " & Timer - t

End Sub

duration using ctrl and tag 0.04296875
duration using ctrl and typeof 0.12109375
duration using object 0.15234375

So basically unnoticeable, contrary to what I expected. So although the proposed code is better, I am not sure if that is the real problem.

 

[chunter33]

No it isn't. Like I said, looping through the controls and finding the ones I want wasn't the problem.

The only problem was this:

Text_control.controlsource = ""

It's unbelievably slow. Please note, that the forms recordsource is a linked table. The recordsource of the form is contantly being changed to point to a new record as dictated by the where clause when the user makes a selection.

Originally, this function was being used as a way to refresh the control source when the forms rowsource changed. Essentially the controlsource would be unbounded, and then rebounded programatically in another function.

I found a much faster way of doing this: Simply set the control source for each of the controls in design time and replace the function that refreshes the controlsources which was originally this:

Text_control.controlsource = "Some table"

With this:

Text_control.requery

This allows me to remove the clearform() function entirely and reduce time by more than a minute.

 

[MajP]

I found a much faster way of doing this: Simply set the control source for each of the controls in design time and replace the function that refreshes the controlsources which was originally this:

Yeah, that is what most people would start with. We assumed you had a purpose for the way you were doing it.

You missed my point. Although your code is inefficient and not well written it really had no impact on the speed. Setting the control source to "" should be basically instantaneous. The real issue probably has to do with the network and breaking the persistent connection to the backend table.

 

[chunter33]

Though that would be possiblem, I don't think the network has anything to do with it. I'm not currently running this code off of a network for fear of modifying live data. For the time being I have a copy of the backend database on my local disk and I simply re-linked the tables as needed. The problem definietly lies with setting the control source to "" I'm afraid.

I'm not sure why the original developer chose to have on the control sources dynamically populated. All I know is that Access 2007 doesn't like it.

 

[chunter33]

I appologise if that last post made me sound a little full of myself, I'm actually fairly new to Access developement. But I'm 99.9% sure that the problem lies with dynamically modifying the control source. If somebody has another theory please inform me as to how I can confirm it.