How to iterate through all forms in a closing event of a form?

[prabha_friend]

Hello friends. I am getting this error “Variable required - can't assign to this expression” when I tries to iterate through all the open form in a close event of a form.

Private Sub Form_Close(Cancel As Integer)
For Each Form In Forms
For Each Control In Form.Controls
If Control.ControlType = 112 Then
For i = 0 To Control.Controls.Count - 1
If Control.Controls(i).Properties("Tag") = "Employees" Then Control.Controls(i).Requery
Next i
End If
If Control.Properties("Tag") = "Employees" Then Control.Requery
Next Control
Next Form
End Sub

I even tried the Unload event also. The Same error returned.

 

[wazz]

something like:

    Const conDesignView = 0
    Dim frm As Form
    Dim ctl As Control
    Dim prp As Property
    
    ' Enumerate Forms collection.
    For Each frm In Forms
        If Forms(frm.Name).CurrentView <> conDesignView Then
            For Each ctl in Forms(frm.Name)
                If ctl.ControlType ...
 
                    ' Enumerate Properties collection of each ctl. <-- haven't checked this
                    For Each prp In ctl.Properties
                    Next prp
        End If
    Next frm

 

[prabha_friend]

Friend. Thank you for answer. Do we have to declare it? Actually before your suggestion I used the same procedure in a public module as a function procedure. It worked. I want to understand the concept why we are able to iterate throught all the forms without declaring it in a public module but not in an event procedure in a form. What's the difference? Thank you for your answer.

 

[wazz]

pretty sure it should work either way and that the main problem was just that you were missing Forms(frm.Name).

 

[prabha_friend]

The actual problem is not in getting the properties. I believe it is in accessing the objects in a collection. We can't access other forms through the form collection with in a form. Because the error says like this Compile error: Variable required - can't assign to this expression.” I have a little doubt that Is form collection and the form objects with in the collection are globals?

 

[wazz]

i haven't tried the routine as it is. i'm wondering, which line does it break on?

was just looking more closely, i don't think this line is right:
For i = 0 To Control.Controls.Count - 1
i think it should be
For i = 0 To Form.Controls.Count - 1

actually, i think it should be

For Each Form In Forms
  For Each Control In Form.Controls
    If Control.ControlType = 112 Then
      [COLOR=green]'For i = 0 To Control.Controls.Count - 1  <-- not necessary[/COLOR]
      If Control.Properties("Tag") = "Employees" Then Control.Requery [COLOR=green]' <-- rewritten[/COLOR]
      End If
    End if
      [COLOR=green]'If Control.Properties("Tag") = "Employees" Then Control.Requery  <-- not necessary
[/COLOR]  Next Control
Next Form

 

[wazz]

i haven't tried the routine as it is. i'm wondering, which line does it break on?

was just looking more closely, i don't think this line is right:
For i = 0 To Control.Controls.Count - 1
i think it should be
For i = 0 To Form.Controls.Count - 1

actually, i think it should be

For Each Form In Forms
  For Each Control In Form.Controls
    If Control.ControlType = 112 Then
      [COLOR=green]'For i = 0 To Control.Controls.Count - 1  <-- not necessary[/COLOR]
      If Control.Properties("Tag") = "Employees" Then Control.Requery [COLOR=green]' <-- rewritten[/COLOR]
      End If
    End if
      [COLOR=green]'If Control.Properties("Tag") = "Employees" Then Control.Requery  <-- not necessary
[/COLOR]  Next Control
Next Form

 

[prabha_friend]

It breaks here itself "For Each Form In Forms" Highlighting the word "Form" and saying "Compile error: Variable required - can't assign to this expression.” But its works fine when I put the same code in a public function module.