473,554 Members | 2,920 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Copy field from 1 subform to another

Hi all,

I have tabbed subforms where I need to copy one field value from one
subform over to another subform. Although I can run an update query to
accomplish this I would like to do it through VBA.

sub tables relationships are 1:N with the main table.

So the recods display like this in the subform:

Subform1
Record Data
1 field1
2 field1
3 field1
Subform2
Record Data
1 field1
2 field1
3 field1

What I would like to do is if I go to record#2 field1 in the first
subtable and change the data I would like to put a vba after update
statment on that field that would copy over the field value to
subtable 2, record2 field1

So the data is identical and it keeps the users from having to input
it twice.

Any ideas?

Sep 26 '07 #1
3 9228
On Sep 26, 4:59 pm, Richnep <Rich...@gmail. comwrote:
Hi all,

I have tabbed subforms where I need to copy one field value from one
subform over to another subform. Although I can run an update query to
accomplish this I would like to do it through VBA.

sub tables relationships are 1:N with the main table.

So the recods display like this in the subform:

Subform1
Record Data
1 field1
2 field1
3 field1
Subform2
Record Data
1 field1
2 field1
3 field1

What I would like to do is if I go to record#2 field1 in the first
subtable and change the data I would like to put a vba after update
statment on that field that would copy over the field value to
subtable 2, record2 field1

So the data is identical and it keeps the users from having to input
it twice.

Any ideas?
sure. read Keri Hardwick's article on referencing subform controls at
www.mvps.org The answer is there.

Sep 26 '07 #2
On Sep 26, 7:07 pm, "pietlin...@hot mail.com" <pietlin...@hot mail.com>
wrote:
On Sep 26, 4:59 pm, Richnep <Rich...@gmail. comwrote:


Hi all,
I have tabbed subforms where I need to copy one field value from one
subform over to another subform. Although I can run an update query to
accomplish this I would like to do it through VBA.
sub tables relationships are 1:N with the main table.
So the recods display like this in the subform:
Subform1
Record Data
1 field1
2 field1
3 field1
Subform2
Record Data
1 field1
2 field1
3 field1
What I would like to do is if I go to record#2 field1 in the first
subtable and change the data I would like to put a vba after update
statment on that field that would copy over the field value to
subtable 2, record2 field1
So the data is identical and it keeps the users from having to input
it twice.
Any ideas?

sure. read Keri Hardwick's article on referencing subform controls atwww.mvps.org The answer is there.- Hide quoted text -

- Show quoted text -
Thanks, I was kinda looking for some more specific direction as I am a
VBA newbie.

Sep 27 '07 #3
Richnep wrote:
On Sep 26, 7:07 pm, "pietlin...@hot mail.com" <pietlin...@hot mail.com>
wrote:
>>On Sep 26, 4:59 pm, Richnep <Rich...@gmail. comwrote:

>>>Hi all,
>>>I have tabbed subforms where I need to copy one field value from one
subform over to another subform. Although I can run an update query to
accomplish this I would like to do it through VBA.
>>>sub tables relationships are 1:N with the main table.
>>>So the recods display like this in the subform:
>>>Subform1
Record Data
1 field1
2 field1
3 field1
Subform2
Record Data
1 field1
2 field1
3 field1
>>>What I would like to do is if I go to record#2 field1 in the first
subtable and change the data I would like to put a vba after update
statment on that field that would copy over the field value to
subtable 2, record2 field1
>>>So the data is identical and it keeps the users from having to input
it twice.
>>>Any ideas?

sure. read Keri Hardwick's article on referencing subform controls atwww.mvps.org The answer is there.- Hide quoted text -

- Show quoted text -


Thanks, I was kinda looking for some more specific direction as I am a
VBA newbie.
An example of how to reference
MF = MainForm
SF1 = Subform1
SF2 = Subform2

Forms!MF!SF1!So meTextBoxInSF1 = Forms!MF!SF2!So meTextBoxInSF2

Of course, specifying an update of a field will require a bit more info.
You can't, for example, do
DoCmd.GoToRecor d acDataForm, "SF2", 3
or
DoCmd.GoToRecor d acDataForm, "Forms!MF!S F2", 3
because you are using subforms, not single forms.

You might need to access the recordset of the subform you want to
update. Let's say you have a field called SF1ID as the primary key for
Subform1. Using the AbsolutePositio n you are assuring that you get the
record position of the current record in the recordset. Then scan
through the recordset to update and find that position. If you see some
stuff you don't understand below after you can highlite the word and
press F1 to get help.

AfterUpdate()
Dim rst2 As Recordset
Dim rst1 As Recordset

Set rst2 = Forms!MF!SF2.Fo rm.RecordsetClo ne
Set rst1 = Forms!MF!SF1.Fo rm.RecordsetClo ne

'find the current record
rst1.findfirst "SF1ID = " & Me.SF1ID

rst2.MoveFirst
Do While Not rst2.EOF
If rst2.AbsolutePo sition = rst1.AbsolutePo sition then
rst2.Edit
rst2!Field2Upda te = Me.SF1Field
rst2.Update
Exit Do
Else
rst2.MoveNext
Endif
Loop
rst1.close
rst2.close
Set rst1 = Nothing
Set rst1 = Nothing
End Sub
Sep 27 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
3318
by: William Wisnieski | last post by:
Hello Everyone, Access 2000 I have a main form with a continuous subform. On the main form I have a text field called . It gets populated based on what the user selects in a field on the sub form (code in the AfterUpdate Event). Here's the issue. If the user deletes a subform record. The field on the main form shows null even if...
3
14498
by: William Wisnieski | last post by:
Hello Again, I'm really stuck on this one.....so I'm going to try a different approach to this problem. I have a query by form that returns a record set in a datasheet. The user double clicks on a row and a main form (pop up) opens bound to a table with a continuous subform bound to another table. On the main form is a field called . ...
2
5761
by: Michelle | last post by:
Hi all I have used knowledge base article 112747 to improve my subforms performance. I am using Access 97. I have a main form where i put an extra textbox which concatenated TeamID WeekNum Weekday, ie value might be 15Monday. I then put in a field in my query behind my subform which held the same info and used these fields to link the...
25
10182
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab...
3
3202
by: david | last post by:
Hi, I've been reading tons of posts on how to copy records, but to no avail....i'm still stuck. There are three tables: Main, Sub-Form1 & Sub-Form2 I have a form which displays some data. The PK (ProductID) for each record on the form is an AUTONUMBER. This form also has a sub-form, linked via ProductID. The subform also
1
3672
by: John Phelan-Cummings | last post by:
I'm not certain if this made the post. Sorry if it's a repeat: Using a Button to take an autonumber from one form to populate another autonumber field on another form. I have a Mainform "A" with a button and code that opens another Mainform "B" with a subform which has the same table as Mainform "A". The button is supposed to tell the...
3
5389
lwwhite
by: lwwhite | last post by:
(Access 2003) Here's what I want to do: enable users to double-click a field in a datasheet-view subform and thereby save the contents of that field to the Windows clipboard so that they can then paste it into another field. I tried to set it up as a DblClick event on the field with this syntax: DoCmd.DoMenuItem acFormBar, acEditMenu, acCopy, ,...
9
9673
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a master-child link to the first subform. subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK Child Field: TrainingModuleTopicSK
0
1529
by: carlton129 | last post by:
Hi! I am trying to copy the current form and subform to the clipboard with the click of a button so that I can paste it in another application. So far, I can get all of the Form info together into a memo using the setvalue feature in a macro. Then, I setfocus to that memo field and run the copy command. I get it all except for the info in the...
0
7578
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7780
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8010
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6119
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5414
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3539
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1111
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
812
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.