Copy Data from SubForm Field to Main Form Field

  • Follow


How do you tell Access to copy data from a field in a subform to a similar 
field in the main form?
0
Reply Utf 3/19/2008 8:17:01 PM

See www.allenbrowne.com

"ridgerunner" wrote:

> How do you tell Access to copy data from a field in a subform to a similar 
> field in the main form?
0
Reply Utf 3/19/2008 8:30:01 PM

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" wrote:

> See www.allenbrowne.com
> 
> "ridgerunner" wrote:
> 
> > How do you tell Access to copy data from a field in a subform to a similar 
> > field in the main form?
0
Reply Utf 3/20/2008 1:44:01 AM

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

"ridgerunner" wrote:

> 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" wrote:
> 
> > See www.allenbrowne.com
> > 
> > "ridgerunner" wrote:
> > 
> > > How do you tell Access to copy data from a field in a subform to a similar 
> > > field in the main form?
0
Reply Utf 3/20/2008 12:28:02 PM

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.

"Golfinray" wrote:

> He has one there that will do exactly what you need.
> 
> "ridgerunner" wrote:
> 
> > 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" wrote:
> > 
> > > See www.allenbrowne.com
> > > 
> > > "ridgerunner" wrote:
> > > 
> > > > How do you tell Access to copy data from a field in a subform to a similar 
> > > > field in the main form?
0
Reply Utf 3/20/2008 10:55:00 PM

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



0
Reply Utf 3/21/2008 7:13:03 AM

Thank you.  I will give this a try.

"Tank" wrote:

> 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
> 
> 
> 
0
Reply Utf 3/21/2008 2:17:01 PM

6 Replies
715 Views

(page loaded in 0.103 seconds)


Reply: