I have no idea... modules... blah...

[jlgray0127]

I took this module off a forum. I have a main form, with one control, and six subforms linked to the once control.


I want to run a module, and the code is below to refresh all the subforms when the user is done updating in the subforms. It does exactly what I want, with the exception, this error pops up when it refreshes.

438
Object doesn't support this property or method

The code is below. This is saved as a module.
Option Compare Database
Public Sub RefreshForms()
On Error GoTo ERR_HANDLER

Dim i As Integer
For i = 0 To Forms.Count - 1
Call RefreshForm(Forms(i))
Next

EXIT_PROC:
Exit Sub

ERR_HANDLER:
MsgBox Err & vbCrLf & Error$
Resume EXIT_PROC:
End Sub


Private Sub RefreshForm(frmForm As Form)
On Error GoTo ERR_HANDLER

Dim i As Integer
With frmForm
.Recalc
For i = 0 To .Controls.Count - 1
Select Case .Controls(i).ControlType
Case acComboBox, acListBox, acSubform
.Controls(1).Requery
End Select
Next
.Refresh
End With

EXIT_PROC:
Exit Sub

ERR_HANDLER:
If Err.Number = 2478 Then
Resume Next
Else
MsgBox Err & vbCrLf & Error$
Resume EXIT_PROC
End If
End Sub

[ssanfu]

Being picky, you don't "run" a module. A module is a container that holds functions and subroutines.

<snip>
For i = 0 To .Controls.Count - 1
       Select Case .Controls(i).ControlType
         Case acComboBox, acListBox, acSubform
           .Controls(1).Requery  <<<<<<---------------------
       End Select
     Next
<snip>

I think the error is because you have a 1 (one) instead of an i (eye). The code should look like:

<snip>
For i = 0 To .Controls.Count - 1
        Select Case .Controls(i).ControlType
          Case acComboBox, acListBox, acSubform
            .Controls(i).Requery    <<<<<<---------------------
        End Select
      Next
<snip>

The code you posted is (IMO) overkill.
You could add a button on the form and add this code to the click event of the button:
(this only requeries subforms)

   Dim i As Integer

   With Me
      For i = 0 To .Controls.count - 1
         Select Case .Controls(i).ControlType
            Case acSubform
               .Controls(i).Requery
         End Select
      Next
      .Refresh
   End With

[orange]

I agree with Steve's comments -seems you got some pretty generic, bullet-proof stuff from a forum. It also seems you don't fully understand what it does. For your own benefit and learning, I would recommend you work through the code and add some comments as to what each line/activity does.

You can research breakpoints and stepping through vba code.

see http://www.cpearson.com/excel/DebuggingVBA.aspx for an excellent guide with examples.

[jlgray0127]

That was the fix!!! The thank you so much!