Copy field from 1 subform to another

[Richnep]

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?

[pietlinden]

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.

[Richnep]

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.

[Salad]

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