• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

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")
0
wlwebb
Asked:
wlwebb
  • 5
  • 3
  • 3
  • +1
1 Solution
 
IrogSintaCommented:
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?
0
 
wlwebbAuthor Commented:
Yes.ade up name to get concept
0
 
IrogSintaCommented:
How about:
Me("sfrm_Address" & i).Form("sfrm_AddressLine" & i).Form("StreetNbr" & i).Value
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
mbizupCommented:
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.
0
 
Dale FyeCommented:
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.
0
 
wlwebbAuthor Commented:
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......
0
 
mbizupCommented:
So you're dealing with sibling forms?  The syntax depends on where the code is located.

IF the code is on the main form, the syntax for subforms is:

Me.controls("subformname" & i).Form.Controls("txtTextbox" & format(i, 00))

Open in new window

If the code is on a subform, referring to a sibling subform:

Me.Parent.controls("subformname" & i).Form.Controls("txtTextbox" & format(i, 00))

Open in new window

0
 
IrogSintaCommented:
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")
0
 
Dale FyeCommented:
@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
0
 
wlwebbAuthor Commented:
Sibling Forms...........  That is a perfect description of what it is!
0
 
wlwebbAuthor Commented:
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.
0
 
Dale FyeCommented:
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")
0
 
wlwebbAuthor Commented:
Thanks to all!!!!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now