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

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

P: 12
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:
Expand|Select|Wrap|Line Numbers
  1. Forms!Mainform!Subform.Requery 
In this syntax, how do I write the code to replace the word ‘Subform’ with the value in the textbox?
Jan 23 '19 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. 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!

Share this Question
Share on Google+
18 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,055
privateguy,

It may be as simple as:

Expand|Select|Wrap|Line Numbers
  1. Forms!Mainform!(Me.txtBoxName).Requery
An interesting approach that I have not seen before.
Jan 23 '19 #2

P: 12
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."
Jan 23 '19 #3

P: 12
twinnyfo,

I tried using brackets as well. Using "me" causes another error.
Removing the "me" causes the error that it cannot find the field.
Jan 23 '19 #4

twinnyfo
Expert Mod 2.5K+
P: 3,055
Try

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 23 '19 #5

P: 12
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.
Jan 23 '19 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Forms(MainFormName).Controls(SubFormName).Form
Thus, to Requery, use :
Expand|Select|Wrap|Line Numbers
  1. Forms(MainFormName).Controls(SubFormName).Form.Requery
Or even :
Expand|Select|Wrap|Line Numbers
  1. Forms(MainFormName).Controls(SubFormName).Requery
Jan 24 '19 #7

P: 12
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.
Jan 24 '19 #8

NeoPa
Expert Mod 15k+
P: 31,186
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.
Jan 24 '19 #9

twinnyfo
Expert Mod 2.5K+
P: 3,055
One more shot:

Expand|Select|Wrap|Line Numbers
  1. Forms!Mainform(Me.txtBoxName).Requery
I forgot to remove the “!” In the previous examples.
Jan 25 '19 #10

Rabbit
Expert Mod 10K+
P: 12,315
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?
Jan 25 '19 #11

P: 12
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.
Jan 25 '19 #12

P: 12
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.
Jan 25 '19 #13

P: 12
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.
Jan 25 '19 #14

NeoPa
Expert Mod 15k+
P: 31,186
PrivateGuy:
In this syntax, how do I write the code to replace the word ‘Subform’ with the value in the textbox?
Let's review :
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 26 '19 #15

twinnyfo
Expert Mod 2.5K+
P: 3,055
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:

Expand|Select|Wrap|Line Numbers
  1. 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!
Jan 26 '19 #16

P: 12
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.
Jan 27 '19 #17

twinnyfo
Expert Mod 2.5K+
P: 3,055
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!
Jan 27 '19 #18

NeoPa
Expert Mod 15k+
P: 31,186
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.) :
Expand|Select|Wrap|Line Numbers
  1. Forms!frmA.Controls(Forms!frmA!txtB)
However, I can't do that using the ! syntax exclusively. The following :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 28 '19 #19

Post your reply

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