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

[Gail Williams]

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:

Private Sub Command8_Click()
 
‘ close the sub form
    DoCmd.Close
‘close the main form
    DoCmd.Close
 
‘Open the main form
    DoCmd.OpenForm "frmUIMain"
 
‘change the subform
Dim strSourceSubForm As String
 
strSourceSubForm = "frmUpdate"
 
With Me!Child20
    .SourceObject = strSourceSubForm
    .LinkMasterFields = ""
    .LinkChildFields = ""
    .Requery
 
End With
End Sub
 

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

[TheSmileyCoder]

I will start with a few general comments on your code:

close the sub form
DoCmd.Close
close the main form
DoCmd.Close
 

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.

Docmd.Close acForm, Me.Parent.Name

could be an alternative.

With Me!Child20
  .SourceObject = strSourceSubForm
  .LinkMasterFields = ""
  .LinkChildFields = ""
  .Requery
End With
 

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

LinkMasterFields 

and

LinkChildFields 

can ONLY be set in design view.

.Requery 

is not needed, a form is queried when it is loaded.



An alternative approach:

With Forms("frmUIMain").Child20
  .SourceObject = strSourceSubForm
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!

[Gail Williams]

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:

    DoCmd.OpenForm "frmUIMain"

As I said, I'll give it a go and let you know.

Thanks again

[Gail Williams]

Hi

Thanks to TheSmileyCoder, I have followed his advise and now got it working. The code ended up as:

Private Sub Command8_Click()
 
Dim strSourceSubForm As String
strSourceSubForm = "frmUpdate"
 
With Forms("frmUIMain").Child20
  .SourceObject = strSourceSubForm
End With
 
End Sub

Thanks again for the assistance.

[NeoPa]

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.