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
392 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 (Access MVP)
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 500 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 (Access MVP)
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 (Access MVP)
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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