How to iterate through all forms in a closing event of a form? (1 Viewer)

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 15:36
Joined
Mar 22, 2009
Messages
771
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

Super Moderator
Local time
Today, 18:06
Joined
Jun 29, 2004
Messages
1,711
something like:
Code:
    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

Prabhakaran Karuppaih
Local time
Today, 15:36
Joined
Mar 22, 2009
Messages
771
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

Super Moderator
Local time
Today, 18:06
Joined
Jun 29, 2004
Messages
1,711
pretty sure it should work either way and that the main problem was just that you were missing Forms(frm.Name).
 

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 15:36
Joined
Mar 22, 2009
Messages
771
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

Super Moderator
Local time
Today, 18:06
Joined
Jun 29, 2004
Messages
1,711
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
Code:
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

Super Moderator
Local time
Today, 18:06
Joined
Jun 29, 2004
Messages
1,711
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
Code:
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

Prabhakaran Karuppaih
Local time
Today, 15:36
Joined
Mar 22, 2009
Messages
771
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.
 

Users who are viewing this thread

Top Bottom