Solved Passing Form as parameter and requerying its subform

[ysdai]

How do you write a sub procedure that, when you pass a form's name or a form object as the parameter, it checks to see if the form is loaded, then requery its underlying subform?

This is what I was trying to do, hard-coded:

Forms!frmMain1.subForm1.Form.Requery

Ultimately, I'd like to come up with a sub procedure like this:

Sub UpdateForm(frm As String)
    Dim subfrm As String
    subfrm = DLookUp("SubFormName","FormsLookup","MainForm='" & frm & "'")
    If IsFormLoaded(frm) Then Forms(frm).subfrm.Form.Requery
End Sub

UpdateForm("frmMain1")

But of course, this code doesn't work. It'll do if I were just requerying the main form, but not the subform. I don't know how to use a variable in place of the subform.

Any ideas?

 

[CJ_London]

you pass a form as a form

Sub UpdateForm(frm As form)
    frm.requery
End Sub

 

[sonic8]

How do you write a sub procedure that, when you pass a form's name or a form object as the parameter, it checks to see if the form is loaded, then requery its underlying subform?

This is what I was trying to do, hard-coded:

Forms!frmMain1.subForm1.Form.Requery

You either need to pass the subform name to the procedure and do this:

Sub UpdateForm(frm As String, subfom as strring)
    Forms(frm).Controls(subform).Form.Requery
End Sub

Or you need to loop through all controls of the (parent) form to find the subform.
Or you pass the subform as Form object to the procedure from the calling code. - If this is possible, I see little value in the procedure as would be able to just requery the subform directly.

 

[arnelgp]

Sub UpdateForm(ByVal frmName As String)
    Dim frm As Form
    Dim ctl As Control
    Dim col As New Collection
    Dim i As Integer
    On Error GoTo err_handler
    If isFormLoaded(frmName) then
        Set frm = Forms(frmName)
   
        For Each ctl In frm
            If TypeOf ctl Is SubForm Then
                i = i + 1
                col.Add Item:=ctl, Key:=i & ""
            End If
        Next
    End If
    For i = 1 To col.Count
        col(i & "").Requery
    Next
    Set frm = Nothing
err_handler:
End Sub

Public Function isFormLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject
    On Error Resume Next
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    isFormLoaded = False
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then isFormLoaded = True
    End If
    Set oAccessObject = Nothing
End Function

 

[ysdai]

I'm not at work right now so I'll just post a quick reply.

CJ_London:
I did that already. It only requeries the main form, not the underlying subform.

Sonic8:
A subform as one of the controls in the main form! I hadn't thought of that at all. If it were combobox that needed to requery, I probably would have tried that already. This just might work. I'll try it out first thing in the morning.

arnelgp:
Again subform as a control. How come I didn't try that approach. This one loops through all the controls and requeries every subform in sight (or so I think). With this code I suppse I don't have to look up subform names. I do have a question though. Why do you need to save into collection and then loop through the collection again to requery? If I just put it in one loop:

For Each ctl In frm
    If TypeOf ctl Is SubForm then ctl.Form.Requery
Next ctl

Would that work as well? And also, what's the purpose of adding a zero string after the i (I & "")?

 

[arnelgp]

Would that work as well?

it will, i am just saving it for later.

zero string after the i (I & "")?

key of a collection is string.

you dont need to use:

ctl.Form.Requery

since ctl is already an object, you can just use:

ctl.Requery

 

[ysdai]

key of a collection is string.

So I suppose it's something like CStr(i), converting another data type into string type? So in that sense, null & "" would also force Null to become a zero string? If so, this technique can be very useful when handling the nulls.

you dont need to use:
ctl.Form.Requery
since ctl is already an object, you can just use:
ctl.Requery

Got it. I have a solution forming in my head now. All I need to do is implement it tomorrow. Will post my results. Thanks.

 

[ysdai]

For Each ctl In frm
    If TypeOf ctl Is SubForm then ctl.Form.Requery
Next ctl

This is the approach I used and now it works perfectly. Thanks for giving me the idea of handling a subform as a control in a form, and not a form within a form. I got stuck because I treated it as a form. Everything was untangled nicely once I got in the right direction.

One thing worth mentioning though. The "Form" was actually needed in ctl.Form.query, without it, the requery won't happen. You can tell if a subform has been requeried by observing if the current row jumps back to the first record. If the current row stayed where it was, the requery didn't happen. Beats me why you need the "Form" there for it to work, but there it is.

 

[arnelgp]

you are right about ctl.Form.

i update it so if the subform has subforms on it, it will get requeried also:

Sub UpdateForm(ByVal frmName As String)
    Dim frm As Form
    On Error GoTo err_handler
    If isFormLoaded(frmName) Then
        Set frm = Forms(frmName)
        Call updForm(frm)
    End If
    Set frm = Nothing
err_handler:
End Sub

Private Sub updForm(ByRef frm As Form)
    Dim ctl As Control
    For Each ctl In frm.Controls
        If TypeOf ctl Is subForm Then
            Call updForm(ctl.Form)
            ctl.Form.Requery
        End If
    Next
End Sub

Public Function isFormLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject
    On Error Resume Next
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    isFormLoaded = False
    If oAccessObject.IsLoaded Then
        If oAccessObject.CurrentView <> acCurViewDesign Then isFormLoaded = True
    End If
    Set oAccessObject = Nothing
End Function

 

[ysdai]

i update it so if the subform has subforms on it, it will get requeried also:

This is obviously a far more sophisticated solution than my crappy version. I'll take this instead. Will this work if there are subforms in tabs? I don't have that kind of form yet, just wondering.

 

[arnelgp]

Will this work if there are subforms in tabs?

i believe it will.