calling a public function correctly

  • Thread starter Thread starter lambertsm via AccessMonster.com
  • Start date Start date
L

lambertsm via AccessMonster.com

I can’t figure out how to run this for the life of me.

Trying to run a function that updates the combo boxes for all open forms.
Here is what I have with asterisks marked on what I think is the problem line.
Have tried many permutations and gotten all sorts of new and exciting errors
but none that has worked right yet.

Sub AllForms()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
*******What goes here to call the requeryCombos function?***********
End If
Next obj
End Sub

Public Function RequeryCombos(frm As Form)
' Requery all combo and list boxes on
'the argument form
' and on any subforms it may contain, and any of their
' subforms, and so on.
Dim ctl As Control
With frm
For Each ctl In .Controls
Select Case ctl.ControlType
Case acListBox, acComboBox
ctl.Requery
Case acSubform
If Len(ctl.SourceObject) > 0 Then
RequeryCombos ctl.Form
End If
End Select
Next ctl
End With

End Function

Any insights would be much appreciated!
Shauna
 
Try something like this:
Set frm = obj
Call RequeryCombos(frm)

That will require another declaration at the top:
Dim frm As Form

This approach won't requery your subforms. To do that you need a recursive
call for each form. Here's an example of a recursive call for all forms in
subforms of the form under consideration:
http://allenbrowne.com/ser-56.html
 
lambertsm via AccessMonster.com said:
I can’t figure out how to run this for the life of me.

Trying to run a function that updates the combo boxes for all open forms.
Here is what I have with asterisks marked on what I think is the problem
line.
Have tried many permutations and gotten all sorts of new and exciting
errors
but none that has worked right yet.

Sub AllForms()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentProject
' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
*******What goes here to call the requeryCombos function?***********
End If
Next obj
End Sub

Public Function RequeryCombos(frm As Form)
' Requery all combo and list boxes on
'the argument form
' and on any subforms it may contain, and any of their
' subforms, and so on.
Dim ctl As Control
With frm
For Each ctl In .Controls
Select Case ctl.ControlType
Case acListBox, acComboBox
ctl.Requery
Case acSubform
If Len(ctl.SourceObject) > 0 Then
RequeryCombos ctl.Form
End If
End Select
Next ctl
End With

End Function

Any insights would be much appreciated!
Shauna
Click to expand...


Here's how to do it using the code framework you started with (though I have
an alternate suggestion which I'll post afterward):

'----- start of code snippet 1 -----
Sub RequeryAllOpenCombos()

Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentProject

' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
RequeryCombos Forms(obj.Name)
End If
Next obj

End Sub
'----- end of code snippet 1 -----

But it seems to me that, since you only care about those forms that are
open, you could more easily use the application's Forms collection, which
holds all open forms. Here's how to do it that way:

'----- start of code snippet 2 -----
Sub RequeryAllOpenCombos()

Dim frm As Access.Form

For Each frm In Application.Forms
RequeryCombos frm
Next frm

End Sub
'----- end of code snippet 1 -----

That's all air code, but it should be something pretty close to that.
 
Allen Browne said:
Try something like this:
Set frm = obj
Call RequeryCombos(frm)
Click to expand...

Allen, I don't think that will work. "obj" is an AccessObject, not a Form.
This approach won't requery your subforms. To do that you need a recursive
call for each form.
Click to expand...

But the code Shauna posted *does* have a recursive call to handle subforms:
 
Thanks to both of you! I went with Dirk's code snippet 2, but thanks for
posting the other solutions as it helped me figure out what I was doing wrong.
This form continues to be a sanity saver for me. It's all so simple when you
know the answer . . .

Dirk said:
I can’t figure out how to run this for the life of me.
Click to expand...
[quoted text clipped - 39 lines]
Any insights would be much appreciated!
Shauna
Click to expand...

Here's how to do it using the code framework you started with (though I have
an alternate suggestion which I'll post afterward):

'----- start of code snippet 1 -----
Sub RequeryAllOpenCombos()

Dim obj As AccessObject, dbs As Object

Set dbs = Application.CurrentProject

' Search for open AccessObject objects in AllForms collection.
For Each obj In dbs.AllForms
If obj.IsLoaded = True Then
RequeryCombos Forms(obj.Name)
End If
Next obj

End Sub
'----- end of code snippet 1 -----

But it seems to me that, since you only care about those forms that are
open, you could more easily use the application's Forms collection, which
holds all open forms. Here's how to do it that way:

'----- start of code snippet 2 -----
Sub RequeryAllOpenCombos()

Dim frm As Access.Form

For Each frm In Application.Forms
RequeryCombos frm
Next frm

End Sub
'----- end of code snippet 1 -----

That's all air code, but it should be something pretty close to that.
Click to expand...
 
Oops I mean this Forum. (My forms don't actually keep me very sane.)
Thanks to both of you! I went with Dirk's code snippet 2, but thanks for
posting the other solutions as it helped me figure out what I was doing wrong.
This form continues to be a sanity saver for me. It's all so simple when you
know the answer . . .
[quoted text clipped - 39 lines]
That's all air code, but it should be something pretty close to that.
Click to expand...
Click to expand...
 
Back
Top