Copy Data from SubForm Field to Main Form Field

[ridgerunner]

How do you tell Access to copy data from a field in a subform to a similar
field in the main form?

 

[Golfinray]

See www.allenbrowne.com

 

[ridgerunner]

Are you referring to the section entitled "Referring to Controls on a
Subform"? Will this actually place of copy of the subform field information
into the main form report and, therefore, into the underlying main table?

 

[Golfinray]

He has one there that will do exactly what you need.

 

[ridgerunner]

I see one about duplicating an entire record, and another about referring to
a field on a sub form, but I do not see anything about copying just one field
from a subform to the main form. I am sorry if I am overlooking this. I
need to be able to copy the data from one field in the sub form underlying
table to the main form underlying table.

 

[Tank]

This example works for me. Perhaps you can replicate/adjust it for your needs.

I have a subform that captures office visit dates. I use a separate “logâ€
table (called “OfficeVisitLogâ€) to capture these dates so I can develop a
variety of reports. I use the separate log table as the primary record
source for the subform. But I still want to capture the string of dates in
one text field (size 255) in my principal table, “Personsâ€, for various
reasons, such as keeping a history of such dates with a person’s record after
archiving the record and deleting all date records in the OfficeVisitLog, or
when sharing the person’s record without having to include the many-to-one
log table.

The OfficeVisitLog consists of two fields, LogPersonID and OVDate. The
LogPersonID of the log table links with the PersonID of the Persons table.

My Persons table has a field named OfficeVisitHistory . I want the date
values in the subform to be copied to the OfficeVisitHistory field with each
date separated by a comma. When I create my subform, I, of course, need the
OfficeVisitLog table as the record source. But I also link the Persons table
to the log table, making sure that I place the OfficeVisitHistory field from
the Persons table into the QBE grid of the Query Window along with the OVDate
field from the OfficeVisitLog table.

In Design View of the subform, I create an AfterUpdate Event Procedure after
highlighting the subform’s date field control, named “OVDate†(same name as
the log table’s field). That Event Procedure is a follows:


Private Sub OVDate_AfterUpdate()

' --------------- OFFICE LOG HISTORY -------------------
' The control named “OVDate†uses this Event Procedure to produce a log
' history 'of office visit dates in a text field, OfficeVisitHistory, in
the Persons Table
' with dates formatted as month-day-year.

' Example: 10-10-07, 12-15-07, 2-20-08, 3-19-08
' --------------------------------------------------------------------

If IsNull(OfficeVisitHistory) Then
OfficeVisitHistory = Format([OVDate], "m-d-yy")

ElseIf Not IsNull(OfficeVisitHistory) Then
OfficeVisitHistory = (OfficeVisitHistory) & ", " & Format([OVDate],
"m-d-yy")

End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub


Hope this helps.
Tank

 

[ridgerunner]

Thank you. I will give this a try.