Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-12-27
13
Medium Priority
?
397 Views
Last Modified: 2012-12-29
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
Comment
Question by:wlwebb
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38726141
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
 

Author Comment

by:wlwebb
ID: 38726149
Yes.ade up name to get concept
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38726155
How about:
Me("sfrm_Address" & i).Form("sfrm_AddressLine" & i).Form("StreetNbr" & i).Value
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 61

Expert Comment

by:mbizup
ID: 38726481
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38726697
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
 

Author Comment

by:wlwebb
ID: 38726870
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
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 38726891
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38726901
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38726905
@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
 

Author Comment

by:wlwebb
ID: 38726909
Sibling Forms...........  That is a perfect description of what it is!
0
 

Author Comment

by:wlwebb
ID: 38726916
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38726944
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
 

Author Closing Comment

by:wlwebb
ID: 38728983
Thanks to all!!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question