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

Changing a subform on a main from, from the original subform

P: 12
Hi

I have created a database that uses a menu form which selects difference subforms depending on the button click. That's no problem.

I've now been asked to update an existing database to be in the same format. This database has about 45 different forms so I can’t fit them all on a single menu besides which in many it's a cascade, you have to go from one form to the next which in the original database I achieve by simply opening the next form. So in the updated version I need to find a way to click on a button on the subform which closes that subform and opens another. Thus far the closest I can get is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2.  
  3. ‘ close the sub form
  4.     DoCmd.Close
  5. ‘close the main form
  6.     DoCmd.Close
  7.  
  8. ‘Open the main form
  9.     DoCmd.OpenForm "frmUIMain"
  10.  
  11. ‘change the subform
  12. Dim strSourceSubForm As String
  13.  
  14. strSourceSubForm = "frmUpdate"
  15.  
  16. With Me!Child20
  17.     .SourceObject = strSourceSubForm
  18.     .LinkMasterFields = ""
  19.     .LinkChildFields = ""
  20.     .Requery
  21.  
  22. End With
  23. End Sub
  24.  
But when I run this, the error I get is that it can't find the Child20 to update. I suspect this is because it has moved from frmUIMain into the default subform already, but I'm not sure. Can anyone suggest a way forward? Or confirm it can't be done?

Thanks
Apr 26 '12 #1

✓ answered by TheSmileyCoder

I will start with a few general comments on your code:
Expand|Select|Wrap|Line Numbers
  1. close the sub form
  2. DoCmd.Close
  3. close the main form
  4. DoCmd.Close
  5.  
While this works I would consider it bad practice to use it to close anything but the form itself. I also dont think you can close a subform, but I have never really looked into it.

Expand|Select|Wrap|Line Numbers
  1. Docmd.Close acForm, Me.Parent.Name
could be an alternative.



Expand|Select|Wrap|Line Numbers
  1. With Me!Child20
  2.   .SourceObject = strSourceSubForm
  3.   .LinkMasterFields = ""
  4.   .LinkChildFields = ""
  5.   .Requery
  6. End With
  7.  
How can you expect ME to work, when you have closed the object?
Furthermore
Expand|Select|Wrap|Line Numbers
  1. LinkMasterFields 
and
Expand|Select|Wrap|Line Numbers
  1. LinkChildFields 
can ONLY be set in design view.
Expand|Select|Wrap|Line Numbers
  1. .Requery 
is not needed, a form is queried when it is loaded.



An alternative approach:
Expand|Select|Wrap|Line Numbers
  1. With Forms("frmUIMain").Child20
  2.   .SourceObject = strSourceSubForm
  3. End With

I would suggest a rename of child20 to for example ctrlFormContainer or something else meaningfull!.

Hope you can use this post as constructive criticism!

Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
I will start with a few general comments on your code:
Expand|Select|Wrap|Line Numbers
  1. close the sub form
  2. DoCmd.Close
  3. close the main form
  4. DoCmd.Close
  5.  
While this works I would consider it bad practice to use it to close anything but the form itself. I also dont think you can close a subform, but I have never really looked into it.

Expand|Select|Wrap|Line Numbers
  1. Docmd.Close acForm, Me.Parent.Name
could be an alternative.



Expand|Select|Wrap|Line Numbers
  1. With Me!Child20
  2.   .SourceObject = strSourceSubForm
  3.   .LinkMasterFields = ""
  4.   .LinkChildFields = ""
  5.   .Requery
  6. End With
  7.  
How can you expect ME to work, when you have closed the object?
Furthermore
Expand|Select|Wrap|Line Numbers
  1. LinkMasterFields 
and
Expand|Select|Wrap|Line Numbers
  1. LinkChildFields 
can ONLY be set in design view.
Expand|Select|Wrap|Line Numbers
  1. .Requery 
is not needed, a form is queried when it is loaded.



An alternative approach:
Expand|Select|Wrap|Line Numbers
  1. With Forms("frmUIMain").Child20
  2.   .SourceObject = strSourceSubForm
  3. End With

I would suggest a rename of child20 to for example ctrlFormContainer or something else meaningfull!.

Hope you can use this post as constructive criticism!
Apr 26 '12 #2

P: 12
Thanks for the response, I'll give these suggestions a go and post if it works.

Only one point I have to raise, TheSmileyCoder said:

"How can you expect ME to work, when you have closed the object? "

I expected it to work because after the close I had in fact opened the main form with:
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.OpenForm "frmUIMain"
As I said, I'll give it a go and let you know.

Thanks again
Apr 26 '12 #3

P: 12
Hi

Thanks to TheSmileyCoder, I have followed his advise and now got it working. The code ended up as:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2.  
  3. Dim strSourceSubForm As String
  4. strSourceSubForm = "frmUpdate"
  5.  
  6. With Forms("frmUIMain").Child20
  7.   .SourceObject = strSourceSubForm
  8. End With
  9.  
  10. End Sub
Thanks again for the assistance.
Apr 26 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
Gail:
I expected it (Me.) to work because after the close I had in fact opened the main form with:
Me. refers to the form associated with the module that the code is running within. Although it cannot close until the code has completed executing, it isn't logical to refer to items on the form after a close has been requested. Me. will not allow you to access items on a separate form, no matter which form has the focus.

Subforms cannot be closed. They can be replaced, and also unloaded (by setting the .SourceObject of the subform control to something else), but never closed.
Apr 27 '12 #5

Post your reply

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