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
374 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
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 47

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 47

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 47

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

827 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