Open many forms from one form

[D Giles]

Have found many solutions on this forum to get to this point so finally registered.

I have a form which should load 17 forms.

Private Sub Form_Load()
On Error GoTo Err_Form_Load
DoCmd.Minimize
DoCmd.OpenForm "Reminder Lease Expiry 6mth", acNormal
DoCmd.OpenForm "Reminder Lease Expiry 2mth", acNormal
DoCmd.OpenForm "Reminder Renewal 6mth", acNormal
DoCmd.OpenForm "Reminder Renewal 2mth", acNormal
DoCmd.OpenForm "Reminder Landlord 6mth", acNormal
DoCmd.OpenForm "Reminder Landlord 2mth", acNormal
DoCmd.OpenForm "Reminder Tenant 6mth", acNormal
DoCmd.OpenForm "Reminder Tenant 2mth", acNormal
DoCmd.OpenForm "Reminder Misc 6mth", acNormal
DoCmd.OpenForm "Reminder Misc 2mth", acNormal
DoCmd.OpenForm "Reminder Rent Escalation", acNormal
DoCmd.OpenForm "Reminder Fixed Monthly", acNormal
DoCmd.OpenForm "Reminder Body Corporate Levy", acNormal
DoCmd.OpenForm "Reminder Rent Review", acNormal
DoCmd.OpenForm "Reminder Loan Term Expiry", acNormal
DoCmd.OpenForm "Reminder Loan Repayment", acNormal
Err_Form_Load:
If Err.Number = 2501 Then
DoCmd.Restore
End If
End Sub

The 17 reminder forms run from queries which show reminders past due or as at today's date. If query produces no record the forms close, or if there are due dates sendObject sends the form as an attachment in an email.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
      If IsNull(Me.Tenant) Then
         Cancel = True
Exit_Form_Open:
         Exit Sub
Err_Form_Open:
If Err.Number = 2501 Then
Resume Exit_Form_Open
End If
Exit Sub
End If
End Sub

Private Sub Form_Load()
On Error GoTo Err_Form_Load
DoCmd.SendObject acSendForm, "Reminder Lease Expiry 6mth", acFormatRTF, _
                 "email; email; email; email", "email; email; email", "email", "Tenant Leases: Reminders", , True
Exit_Form_Load:
         Exit Sub
Err_Form_Load:
If Err.Number = 2501 Then
Resume Exit_Form_Load
End If
End Sub

This works fine until reaches a form with 0 records which closes, and then it does not run through rest of forms. Any help? thanks.

[pelicanstuff]

I've done something similar for my project - it might not be what you've been looking for, though.

Private Sub Form_Load()
Me.Visible = False
If DCount("*", "ExpiryQuery1") > 0 Then
    DoCmd.OpenForm "ExpiryForm1", acFormDS, , , acFormReadOnly
  
    Else
        If DCount("*", "ExpiryQuery2") > 0 Then
        DoCmd.OpenForm "Expiryform2", acFormDS, , , acFormReadOnly
        
            Else
            If DCount("*", "ExpiryQuery3") > 0 Then
            DoCmd.OpenForm "ExpiryForm3", acFormDS, , , acFormReadOnly
            
                Else
                If DCount("*", "ExpiryQuery4") > 0 Then
                DoCmd.OpenForm "ExpiryForm4", acFormDS, , , acFormReadOnly
                
                    Else
                    MsgBox "nothing has expired"

                End If
            End If
        End If
End If
Me.Visible = True
End Sub

and then in ExpiryForm1:

Private Sub Form_Close()
If DCount("*", "ExpiryQuery2") > 0 Then
        DoCmd.OpenForm "Expiryform2", acFormDS, , , acFormReadOnly
        
            Else
            If DCount("*", "ExpiryQuery3") > 0 Then
            DoCmd.OpenForm "ExpiryForm3", acFormDS, , , acFormReadOnly
            
                Else
                If DCount("*", "ExpiryQuery4") > 0 Then
                DoCmd.OpenForm "ExpiryForm4", acFormDS, , , acFormReadOnly
                
                    Else
                    MsgBox "nothing has expired"

End If
End If
End If
End Sub

and so forth for the form_close event of forms 2 and 3, so that the forms run in a cycle, and any with no results gets bypassed. The advantage of this is that you're not opening all the forms at once, but if you did want to, I would imagine the code would look something like this:

Private Sub Form_Load()

If DCount("*", "ExpiryQuery1") > 0 Then
DoCmd.OpenForm "ExpiryForm1", acFormDS, , , acFormReadOnly   
If DCount("*", "ExpiryQuery2") > 0 Then
DoCmd.OpenForm "Expiryform2", acFormDS, , , acFormReadOnly               
If DCount("*", "ExpiryQuery3") > 0 Then
DoCmd.OpenForm "ExpiryForm3", acFormDS, , , acFormReadOnly
If DCount("*", "ExpiryQuery4") > 0 Then
DoCmd.OpenForm "ExpiryForm4", acFormDS, , , acFormReadOnly
End If
End If
End If
End If
End Sub

As you can see, I'm opening the forms as datasheets and read-only, that might not be what you're after.

I'm not so hot with access so feel free to take this with a pinch of salt, but it's something I've just had to do as well.

[D Giles]

Thanks pelicanstuff for prompt response.
Just doesn't want to work though - stops short each time after first two forms.

[D Giles]

Thanks pelicanstuff for your prompt response, but I still cant get this to work. I've tried re-arranging the opening order of the Expiry form DoCmds in the main form code, to see where it hangs, and I think it has to do with the Exit or Error handler on the SendObject email sender in the Reminder form code:

Private Sub Form_Load()
On Error GoTo Err_Form_Load
DoCmd.SendObject acSendForm, "Reminder Body Corp Levy", acFormatRTF, _
                 "email; email; email; email", "email; email; email", "email", "Tenant Leases: Reminders", , True
Exit_Form_Load:
         Exit Sub
Err_Form_Load:
If Err.Number = 2501 Then
Resume Exit_Form_Load
End If
End Sub

Any ideas how I should change this? (Access 2003) Thanks.

[D Giles]

I take it back! Thanks! It works! Maybe took a while to get right because 17 different reminders! Thanks again.