By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,079 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,079 IT Pros & Developers. It's quick & easy.

Open many forms from one form

D Giles
P: 11
Have found many solutions on this forum to get to this point so finally registered.

I have a form which should load 17 forms.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error GoTo Err_Form_Load
  3. DoCmd.Minimize
  4. DoCmd.OpenForm "Reminder Lease Expiry 6mth", acNormal
  5. DoCmd.OpenForm "Reminder Lease Expiry 2mth", acNormal
  6. DoCmd.OpenForm "Reminder Renewal 6mth", acNormal
  7. DoCmd.OpenForm "Reminder Renewal 2mth", acNormal
  8. DoCmd.OpenForm "Reminder Landlord 6mth", acNormal
  9. DoCmd.OpenForm "Reminder Landlord 2mth", acNormal
  10. DoCmd.OpenForm "Reminder Tenant 6mth", acNormal
  11. DoCmd.OpenForm "Reminder Tenant 2mth", acNormal
  12. DoCmd.OpenForm "Reminder Misc 6mth", acNormal
  13. DoCmd.OpenForm "Reminder Misc 2mth", acNormal
  14. DoCmd.OpenForm "Reminder Rent Escalation", acNormal
  15. DoCmd.OpenForm "Reminder Fixed Monthly", acNormal
  16. DoCmd.OpenForm "Reminder Body Corporate Levy", acNormal
  17. DoCmd.OpenForm "Reminder Rent Review", acNormal
  18. DoCmd.OpenForm "Reminder Loan Term Expiry", acNormal
  19. DoCmd.OpenForm "Reminder Loan Repayment", acNormal
  20. Err_Form_Load:
  21. If Err.Number = 2501 Then
  22. DoCmd.Restore
  23. End If
  24. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. On Error GoTo Err_Form_Open
  3.       If IsNull(Me.Tenant) Then
  4.          Cancel = True
  5. Exit_Form_Open:
  6.          Exit Sub
  7. Err_Form_Open:
  8. If Err.Number = 2501 Then
  9. Resume Exit_Form_Open
  10. End If
  11. Exit Sub
  12. End If
  13. End Sub
  14.  
  15. Private Sub Form_Load()
  16. On Error GoTo Err_Form_Load
  17. DoCmd.SendObject acSendForm, "Reminder Lease Expiry 6mth", acFormatRTF, _
  18.                  "email; email; email; email", "email; email; email", "email", "Tenant Leases: Reminders", , True
  19. Exit_Form_Load:
  20.          Exit Sub
  21. Err_Form_Load:
  22. If Err.Number = 2501 Then
  23. Resume Exit_Form_Load
  24. End If
  25. End Sub
  26.  
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.
Jan 25 '08 #1
Share this Question
Share on Google+
4 Replies


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


Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Me.Visible = False
  3. If DCount("*", "ExpiryQuery1") > 0 Then
  4.     DoCmd.OpenForm "ExpiryForm1", acFormDS, , , acFormReadOnly
  5.  
  6.     Else
  7.         If DCount("*", "ExpiryQuery2") > 0 Then
  8.         DoCmd.OpenForm "Expiryform2", acFormDS, , , acFormReadOnly
  9.  
  10.             Else
  11.             If DCount("*", "ExpiryQuery3") > 0 Then
  12.             DoCmd.OpenForm "ExpiryForm3", acFormDS, , , acFormReadOnly
  13.  
  14.                 Else
  15.                 If DCount("*", "ExpiryQuery4") > 0 Then
  16.                 DoCmd.OpenForm "ExpiryForm4", acFormDS, , , acFormReadOnly
  17.  
  18.                     Else
  19.                     MsgBox "nothing has expired"
  20.  
  21.                 End If
  22.             End If
  23.         End If
  24. End If
  25. Me.Visible = True
  26. End Sub
and then in ExpiryForm1:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2. If DCount("*", "ExpiryQuery2") > 0 Then
  3.         DoCmd.OpenForm "Expiryform2", acFormDS, , , acFormReadOnly
  4.  
  5.             Else
  6.             If DCount("*", "ExpiryQuery3") > 0 Then
  7.             DoCmd.OpenForm "ExpiryForm3", acFormDS, , , acFormReadOnly
  8.  
  9.                 Else
  10.                 If DCount("*", "ExpiryQuery4") > 0 Then
  11.                 DoCmd.OpenForm "ExpiryForm4", acFormDS, , , acFormReadOnly
  12.  
  13.                     Else
  14.                     MsgBox "nothing has expired"
  15.  
  16. End If
  17. End If
  18. End If
  19. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. If DCount("*", "ExpiryQuery1") > 0 Then
  4. DoCmd.OpenForm "ExpiryForm1", acFormDS, , , acFormReadOnly   
  5. If DCount("*", "ExpiryQuery2") > 0 Then
  6. DoCmd.OpenForm "Expiryform2", acFormDS, , , acFormReadOnly               
  7. If DCount("*", "ExpiryQuery3") > 0 Then
  8. DoCmd.OpenForm "ExpiryForm3", acFormDS, , , acFormReadOnly
  9. If DCount("*", "ExpiryQuery4") > 0 Then
  10. DoCmd.OpenForm "ExpiryForm4", acFormDS, , , acFormReadOnly
  11. End If
  12. End If
  13. End If
  14. End If
  15. 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.
Jan 25 '08 #2

D Giles
P: 11
Thanks pelicanstuff for prompt response.
Just doesn't want to work though - stops short each time after first two forms.
Jan 28 '08 #3

D Giles
P: 11
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. On Error GoTo Err_Form_Load
  3. DoCmd.SendObject acSendForm, "Reminder Body Corp Levy", acFormatRTF, _
  4.                  "email; email; email; email", "email; email; email", "email", "Tenant Leases: Reminders", , True
  5. Exit_Form_Load:
  6.          Exit Sub
  7. Err_Form_Load:
  8. If Err.Number = 2501 Then
  9. Resume Exit_Form_Load
  10. End If
  11. End Sub
Any ideas how I should change this? (Access 2003) Thanks.
Jan 28 '08 #4

D Giles
P: 11
I take it back! Thanks! It works! Maybe took a while to get right because 17 different reminders! Thanks again.
Jan 28 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.