How to replace the name of a form within the syntax with the value in a textbox?

[privateguy]

I have a form that contains 3 sub forms with the names sub1, sub2 and sub3. Each sub form has a button that pops up the same form. When the user clicks on the sub form button, the name of the sub form that called the popup form is stored in a hidden textbox so that the name of the sub form can be used to requery the sub form once the popup form is closed. The syntax to requery the sub form is:

Forms!Mainform!Subform.Requery 

In this syntax, how do I write the code to replace the word ‘Subform’ with the value in the textbox?

[twinnyfo]

privateguy,

It may be as simple as:

Forms!Mainform!(Me.txtBoxName).Requery

An interesting approach that I have not seen before.

[privateguy]

twinnyfo,

Thanks for responding. I wish it was that simple. Unfortunately, that does not work. It causes the following error "Compile error: Expected identifier or bracketed expression."

[privateguy]

twinnyfo,

I tried using brackets as well. Using "me" causes another error.
Removing the "me" causes the error that it cannot find the field.

[twinnyfo]

Try

Forms!Mainform!(" & Me.txtBoxName & ").Requery

I know we are chasing here, but there must be a combination of characters and codes that allow it to recognize the Form.

[privateguy]

It causes an error as well. The "(" after the "!" causes the first error. I tried using a string. I tried assigning a textbox value to a variable, but both did not work as well.

[NeoPa]

Subforms are actually Controls. The Forms on those Subforms are not Subforms - even if they are used as sub-forms.

Syntax for handling Subforms and their forms can be found at Referring to Items on a Sub-Form.

As long as the string passed to the Form that you open is the name of the Subform control, and I would also suggest you pass the name of the main form too for full flexibility, you can use the following syntax to refer to the Subform's Form object :

Forms(MainFormName).Controls(SubFormName).Form

Thus, to Requery, use :

Forms(MainFormName).Controls(SubFormName).Form.Requery

Or even :

Forms(MainFormName).Controls(SubFormName).Requery

[privateguy]

NeoPa, thank you for responding. Your article on how to refer to forms and sub forms is awesome; however, that is not my problem. The syntax that I am using works if I hard code the name of the sub form. My problem is trying to use a variable for the name of the sub form so that I can use the value in the textbox.

[NeoPa]

And you don't believe the answer to that is in the linked article?

Bizarre, but no problem. I also included it more specifically in my post.

Let me know if you still have difficulty seeing your solution.

[twinnyfo]

One more shot:

Forms!Mainform(Me.txtBoxName).Requery

I forgot to remove the “!” In the previous examples.

[Rabbit]

I'm confused as to why you even need to bother with storing the name of the subform in a hidden control. If you have a line of code to set that value in the hidden control, you may as well refresh the subform directly without this extra middle step?

[privateguy]

NeoPa, I did not see the solution to my problem in your post. I understand that sub forms are controls as well as the syntax to reference the appropriate control. Your post does not state, for my problem, how to take the value of the textbox, which can change, and insert it into the syntax without causing an error. I was hoping there was a way to accomplish this with only a few lines of code instead of me having to writing many lines of code if I were to use the Select Case statement. Thank you for trying to help.

[privateguy]

twinnyfo, I tried that as well. The error it produced was that it could not find the field. I have tried using strings, setting the value of the textbox as a control without any success. Unless some other guru knows of a way to do this, I am going to be stuck with using the Select Case statement. I appreciate you giving thought to my problem.

[privateguy]

Rabbit, if I only had one sub form, there would be no need to do it the way that I am; however, if I have let's say 20 sub forms that contain unique data, it makes sense to only re-query the sub form that was edited. I hope this gives you clarity. Thank you for responding.

[NeoPa]

PrivateGuy:
In this syntax, how do I write the code to replace the word ‘Subform’ with the value in the textbox?

Let's review :

Forms(MainFormName).Controls(SubFormName).Requery

I now understand you're having difficulty because the reference to the Control you want to use is not returning the value you expect. That's another question. This one assumes you have the value available.

Now, as far as I can see, you have not shared with us the name of the hidden TextBox you keep referring to. Fine. Unhelpful, but we can handle that. Twinny has used Me.txtBoxName whereas I just used SubFormname. If you need more specific help than that then you really need to start focusing a bit more on giving information rather than just getting it. We try with our hands tied, but just remember who tied them.

Having said all that, I get the strong impression you're trying to be as responsive as you can be. That's always most important.

As a last attempt to give you something you can use I will first explain that you need to update this based on whatever name you've used for the TextBox Control you need to use from your own Form.

Forms("MainForm").Controls(Me.YourTextBox).Requery

So, replace Me.YourTextBox with a reference to your own TextBox. Typically that should start with Me.txt... but that's just good advice and not mandatory. Quotes are not required here as you are referring to a Control on your Form.

If your Control is not returning the value you expect then, obviously, you need to fix that problem before testing this solution. Clearly it will not work unless the basic framework you described originally can be relied on.

[twinnyfo]

PrivateGuy,

Did NeoPa's solution work for you? I received an error when I referred to the Controls....

I have just tested the following syntax and it does, in fact, requery the referred subform:

Forms!frmMainForm.Form(Me.txtFormName).Requery

Replace frmMainForm with the actual name of the main form to which you are referring.

Replace txtFormName with the actual name of the control that holds the name of the subform you want to requery.

Hope this hepps!

[privateguy]

Twinnyfo,

Yes. The structure of NeoPa's syntax as well as yours worked without error! Two different ways of accomplishing the same task. In analyzing them, I see the distinct value of both. For brevity, I like using the ! mark instead of having write out the word Controls.

I give kudos to both you and NeoPa. I say to both of you that I am very appreciative of your expertise in helping me solve this problem. I humbly thank you.

[twinnyfo]

Glad we could be of service. referring to objects can be a bit confusing at times, but once you figure it out, it can be very flexible.

Cheers!

[NeoPa]

PrivateGuy:
Yes. The structure of NeoPa's syntax as well as yours worked without error! Two different ways of accomplishing the same task.

That's not quite accurate, I'm afraid. There is a very real difference. It's all explained in Referring to Items on a Sub-Form but let me see if I can highlight the difference here.

Say I have a Form, frmA, which has a TextBox Control, txtB, as well as three Sub-Form Controls (sfm1, sfm2 & sfm3) on it. Now, I can reference sfm2 from anywhere as either Forms!frmA!sfm2 or Forms("frmA").Controls("sfm2").

However, as I have three such Subform Controls on the Form it turns out I want to use whatever's in txtB to determine which of the Subforms I want to reference. I can do that using (Always assuming the TextBox has a value that matches the name of one of the Subform Controls.) :

Forms!frmA.Controls(Forms!frmA!txtB)

However, I can't do that using the ! syntax exclusively. The following :

Forms!frmA!txtB

simply references the TextBox Control itself, rather than the Subform whose name is the .Value of that TextBox Control.

So, yes, the ! format can be used as a shortcut. But!! It always helps to understand fully what it's doing. If that understanding is lacking then it won't behave as expected.