Avatar of wlwebb
wlwebb
Flag for United States of America asked on

Access - Tabbed Form - VB to use a Variable within code to send value from one tab's Subform to another Tab's Subform

Hello All

Need some clarification. I asked earlier today how to change a code to make a subform name variable so that I could copy multiple pieces of data to multiple subforms (I am making the last two characters of my controls and textboxes numeric sequences so I can get a way to do/for loops)

Now then my data is coming from a Subform on Page1 of my Tabbed Form.
Forms![tfrm_Address]![sfrm_AddressInitialInput].Form![txtStreetName01]
Forms![tfrm_Address]![sfrm_AddressInitialInput].Form![txtStreetName02]


And is going to Other Tabs Subforms of that same Tabbed Form.
Forms![tfrm_Address]![sfrm_Address1].Form![sfrm_AddressLine1].Form![StreetNbr01]
Forms![tfrm_Address]![sfrm_Address2].Form![sfrm_AddressLine2].Form![StreetNbr02]

Earlier Mbiz pointed out using Me.Controls("xxxx" & i) as a way to accomplish that.  However once I moved my form to a Tabbed and split my form up into several Subforms on different tabs, that coding scheme didn't work.

I attempted changing it several ways but it did not like starting with me.controls  

Any idea how I could make that coding variable so that the forms and textboxes with the last characters as 1,2,3,4....could be converted to something like

Me("form" & i).("textbox" & format(i,"00")
Microsoft Access

Avatar of undefined
Last Comment
wlwebb

8/22/2022 - Mon
IrogSinta

Forms![tfrm_Address]![sfrm_Address1].Form![sfrm_AddressLine1].Form![StreetNbr01]

Do I understand correctly that you have a subform called sfrm_AddressLine1 inside a subform called sfrm_Address1 which is inside your main form called tfrm_Address?
wlwebb

ASKER
Yes.ade up name to get concept
IrogSinta

How about:
Me("sfrm_Address" & i).Form("sfrm_AddressLine" & i).Form("StreetNbr" & i).Value
Your help has saved me hundreds of hours of internet surfing.
fblack61
mbizup

Are you using a tab control (on a single form) with subforms on different tabs?

Or are you using several different forms in Tabbed Form View (Access 2007 and higher).

Sreenshots or a sample would help... but if you are having trouble with the Me.Controls(...).Form(txtbox & I) syntax I posted in your last question, it sounds like you are dealing with the latter.

If that is the case then you actually have separate forms (as opposed to subforms), and this may work:

' This enumerates separate Forms / Control Names
Forms("sfrm_Address" & i).Controls("textbox" & format(i,"00")

Open in new window


And if you have separate forms each having subforms:

' This enumerates separate Forms / Subforms / Control Names
Forms("sfrm_Address" & i).Controls("SubformControlName" & i).Form.Controls("textbox" & format(i,"00")

Open in new window


The word "Controls" in those lines is optional, but I generally use it to distinguish between forms and controls present on those forms.  It clarifies that a given object is part of the Controls collection.

Also, when dealing with paths subforms, you should use the "Subform Control Name" which is the name of the control (which looks like a frame) that houses your subform in your form's design view.  The Subform Control Name may be different from the name of the subform as seen in the navigation pane.
Dale Fye

But lets backup.  Why do you need to copy data from one subform to another?  It is generally considered bad practice to enter the same information into more than one table in your database (except of course foreign keys which will point to where the actual data is stored).

So, WHY do you need to do this is probably a better question than HOW do you do it.  If you actually want to view the data in more than one place, then write the data to the table it belongs in and update the query used to view it.
wlwebb

ASKER
MBiz.

I am actually using one form on to which I inserted a Tab Control.  Then on the tabs of that Tab Control I inserted Subforms.

The first Form Only with Subforms I had asked and you answered for me yesterday worked fine.  However, that form was getting a might bit too cluttered and large for what I was doing so I decided that I would split that form up into tabs with subsforms on those tabs.

So I made a new Form [tfrm_dta_Address] and then onto one of the tabs I inserted that old [sfrm_dta_Address] form we originally worked on.  THEN I took that [sfrm_dta_Address] and moved subforms off of it and onto their own tab.  Once I did that, the me.controls coding errored out.


FYED


On my overall tabbed form I did not want record numbers to be created and data getting into tables until ALL of the data for ALL tables effected by this input was input and verified.  In order to accomplish that (at least in my slanted way of thinking....  ( ;-))  ... I made an Unbound input form where by code I verify what is being input and then when they click a command button it moves the data to the various Tabs and Subforms......
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
mbizup

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
IrogSinta

So it looks like you're dealing with subforms inside a main form and not subform inside a subform inside a main form (2 levels down) as you originally answered.

Slight correction to Miriam's answer above is the quotes inside the format function:
Format(i,"00")
Dale Fye

@wlwebb,

I would still encourage you to only display one particular type of data in one place on your form.  You have an addresses tab to display addresses, why would you need to display those elsewhere.  The only reason I can envision having two "address" subforms is if you are doing something like separate billing and shipping addresses, in which case you might put both of those subforms on the same tab, or have separate tabs and need to do what you are discussing.

*******************

if the code you are referring to, which copies data from one subform to another is still behind sfrm_dta_Address, and the subforms which were originally on sfrm_dta_Address are now actually subforms of the main form, then you will have to change the way you refer to the other subforms, from:

me.subformControlName.txtWhatever

to something like:

me.parent.subformControlName.txtWhatever
wlwebb

ASKER
Sibling Forms...........  That is a perfect description of what it is!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
wlwebb

ASKER
Irog

Some of the data is in-fact being copied from on tab subform TO another tab subform's subform...  some just to another tab subform.
Dale Fye

See mbizup's post above.

If the code is behind the main form, then you can refer directly to the subforms with:

me.controls("subform2")

if the code is behind one of the subforms, then you need to refer to the parent before referring to the other child:

me.parent.controls("subform2")
wlwebb

ASKER
Thanks to all!!!!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.