Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

copy data to a new recordset via a subform

Discussion in 'Information Technology' started by wilcoit, Jul 28, 2006.

  1. wilcoit

    wilcoit
    Expand Collapse
    Guest

    I want to copy data from a control on a form (primary table) to a control on
    subform (secondary table)- one to many relationship. The purpose is to create
    a history of the business process by recording critical data via the subform.
    I need to create a new record via the subform every time data on the main
    form
    is changed or added.
    --
    dtw
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Akal Takht concerned on drug addiction, copying during exams Breaking News Nov 5, 2013
    SciTech New Quantum Dot Technique Combines Best of Optical and Electron Microscopy Breaking News Jun 17, 2013
    India Copy of 48th Rreport of 'LOKPAL BILL 2011' Presented in Parliament Breaking News Dec 10, 2011
    Bhagats Sheikh Farid Copyright? History of Sikhism Nov 14, 2011
    Christianity Public library find is only surviving copy of rebel hymn book Interfaith Dialogues Jan 8, 2011

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    You will need to use the events of the main form to append the new record to
    the subform (or to its table.)

    Since you want to use the OldValue (the value before it changed), you will
    need to use the BeforeUpdate event of the form to get the values. However,
    in that event, you don't know if the write will succeed, so the logging must
    take place in Form_AfterUpdate. It therefore requires both events.

    If you need to log deletions as well, things get even more interesting.
    There is a set of routines you can use and an explanation of how to set up
    the logging tables in this article:
    Audit Trail - Log changes at the record level
    at:
    http://allenbrowne.com/AppAudit.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    news:287CD2DC-76CE-45A9-9DFC-48EB44DFC5C3@microsoft.com...
    >I want to copy data from a control on a form (primary table) to a control
    >on
    > subform (secondary table)- one to many relationship. The purpose is to
    > create
    > a history of the business process by recording critical data via the
    > subform.
    > I need to create a new record via the subform every time data on the main
    > form
    > is changed or added.
    > --
    > dtw
     
  4. wilcoit

    wilcoit
    Expand Collapse
    Guest

    Thank you for your reply which is very helpful. My process is very basic and
    simple but I do not justify the thoroughness of your proposal. I just want to
    use the 'after update' function of the source field on the main form to copy
    the contents (eg a date) into a new record (memo type field) on the subform.
    I also wish to do the same thing with a text field to add a note. In other
    words I want to replicate a copy and paste function but do not have the
    knowledge to write the macro.
    --
    dtw


    "Allen Browne" wrote:

    > You will need to use the events of the main form to append the new record to
    > the subform (or to its table.)
    >
    > Since you want to use the OldValue (the value before it changed), you will
    > need to use the BeforeUpdate event of the form to get the values. However,
    > in that event, you don't know if the write will succeed, so the logging must
    > take place in Form_AfterUpdate. It therefore requires both events.
    >
    > If you need to log deletions as well, things get even more interesting.
    > There is a set of routines you can use and an explanation of how to set up
    > the logging tables in this article:
    > Audit Trail - Log changes at the record level
    > at:
    > http://allenbrowne.com/AppAudit.html
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    > news:287CD2DC-76CE-45A9-9DFC-48EB44DFC5C3@microsoft.com...
    > >I want to copy data from a control on a form (primary table) to a control
    > >on
    > > subform (secondary table)- one to many relationship. The purpose is to
    > > create
    > > a history of the business process by recording critical data via the
    > > subform.
    > > I need to create a new record via the subform every time data on the main
    > > form
    > > is changed or added.
    > > --
    > > dtw

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Okay, it sounds like you may not yet have the grasp to get the OldValue
    property of the controls, and create the new record.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    news:725D96D5-C592-4233-85EA-5068A736720E@microsoft.com...
    > Thank you for your reply which is very helpful. My process is very basic
    > and
    > simple but I do not justify the thoroughness of your proposal. I just want
    > to
    > use the 'after update' function of the source field on the main form to
    > copy
    > the contents (eg a date) into a new record (memo type field) on the
    > subform.
    > I also wish to do the same thing with a text field to add a note. In other
    > words I want to replicate a copy and paste function but do not have the
    > knowledge to write the macro.
    > --
    > dtw
    >
    >
    > "Allen Browne" wrote:
    >
    >> You will need to use the events of the main form to append the new record
    >> to
    >> the subform (or to its table.)
    >>
    >> Since you want to use the OldValue (the value before it changed), you
    >> will
    >> need to use the BeforeUpdate event of the form to get the values.
    >> However,
    >> in that event, you don't know if the write will succeed, so the logging
    >> must
    >> take place in Form_AfterUpdate. It therefore requires both events.
    >>
    >> If you need to log deletions as well, things get even more interesting.
    >> There is a set of routines you can use and an explanation of how to set
    >> up
    >> the logging tables in this article:
    >> Audit Trail - Log changes at the record level
    >> at:
    >> http://allenbrowne.com/AppAudit.html
    >>
    >> "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    >> news:287CD2DC-76CE-45A9-9DFC-48EB44DFC5C3@microsoft.com...
    >> >I want to copy data from a control on a form (primary table) to a
    >> >control
    >> >on
    >> > subform (secondary table)- one to many relationship. The purpose is to
    >> > create
    >> > a history of the business process by recording critical data via the
    >> > subform.
    >> > I need to create a new record via the subform every time data on the
    >> > main
    >> > form
    >> > is changed or added.
    >> > --
    >> > dtw
     
  6. wilcoit

    wilcoit
    Expand Collapse
    Guest

    In the 'After Update' of the Source control (Date Added) on the main form I
    have the following code:
    'Get new Notes ID
    Dim lgHID As Long
    lgHID = DMax("[ID]", "tblNotes") + 1

    'so create the Notes record
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM TblNotes", CurrentProject.Connection,
    adOpenKeyset, adLockOptimistic
    rs.AddNew

    rs!ID = lgHID
    rs!Remarks = Me.DateAdded


    rs.Update
    rs.Close
    Set rs = Nothing

    When I exit the control I get the following run time error message:
    "You cannot add or change a record because a related record is required in
    'tblPermits'"
    tblPermits is my Primary table and Table Notes is the related table in the
    subform.

    Is my methodology completely wrong or can code overcome this?
    --
    dtw


    "Allen Browne" wrote:

    > Okay, it sounds like you may not yet have the grasp to get the OldValue
    > property of the controls, and create the new record.
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    > news:725D96D5-C592-4233-85EA-5068A736720E@microsoft.com...
    > > Thank you for your reply which is very helpful. My process is very basic
    > > and
    > > simple but I do not justify the thoroughness of your proposal. I just want
    > > to
    > > use the 'after update' function of the source field on the main form to
    > > copy
    > > the contents (eg a date) into a new record (memo type field) on the
    > > subform.
    > > I also wish to do the same thing with a text field to add a note. In other
    > > words I want to replicate a copy and paste function but do not have the
    > > knowledge to write the macro.
    > > --
    > > dtw
    > >
    > >
    > > "Allen Browne" wrote:
    > >
    > >> You will need to use the events of the main form to append the new record
    > >> to
    > >> the subform (or to its table.)
    > >>
    > >> Since you want to use the OldValue (the value before it changed), you
    > >> will
    > >> need to use the BeforeUpdate event of the form to get the values.
    > >> However,
    > >> in that event, you don't know if the write will succeed, so the logging
    > >> must
    > >> take place in Form_AfterUpdate. It therefore requires both events.
    > >>
    > >> If you need to log deletions as well, things get even more interesting.
    > >> There is a set of routines you can use and an explanation of how to set
    > >> up
    > >> the logging tables in this article:
    > >> Audit Trail - Log changes at the record level
    > >> at:
    > >> http://allenbrowne.com/AppAudit.html
    > >>
    > >> "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    > >> news:287CD2DC-76CE-45A9-9DFC-48EB44DFC5C3@microsoft.com...
    > >> >I want to copy data from a control on a form (primary table) to a
    > >> >control
    > >> >on
    > >> > subform (secondary table)- one to many relationship. The purpose is to
    > >> > create
    > >> > a history of the business process by recording critical data via the
    > >> > subform.
    > >> > I need to create a new record via the subform every time data on the
    > >> > main
    > >> > form
    > >> > is changed or added.
    > >> > --
    > >> > dtw

    >
    >
    >
     
  7. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Yes, that's the wrong event, the wrong timing, and the wrong code.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    news:8E456F7D-65C4-4B67-AB6F-8D46C99AE747@microsoft.com...
    > In the 'After Update' of the Source control (Date Added) on the main form
    > I
    > have the following code:
    > 'Get new Notes ID
    > Dim lgHID As Long
    > lgHID = DMax("[ID]", "tblNotes") + 1
    >
    > 'so create the Notes record
    > Dim rs As New ADODB.Recordset
    > rs.Open "SELECT * FROM TblNotes", CurrentProject.Connection,
    > adOpenKeyset, adLockOptimistic
    > rs.AddNew
    >
    > rs!ID = lgHID
    > rs!Remarks = Me.DateAdded
    >
    >
    > rs.Update
    > rs.Close
    > Set rs = Nothing
    >
    > When I exit the control I get the following run time error message:
    > "You cannot add or change a record because a related record is required in
    > 'tblPermits'"
    > tblPermits is my Primary table and Table Notes is the related table in the
    > subform.
    >
    > Is my methodology completely wrong or can code overcome this?
    > --
    > dtw
    >
    >
    > "Allen Browne" wrote:
    >
    >> Okay, it sounds like you may not yet have the grasp to get the OldValue
    >> property of the controls, and create the new record.
    >>


    >> "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    >> news:725D96D5-C592-4233-85EA-5068A736720E@microsoft.com...
    >> > Thank you for your reply which is very helpful. My process is very
    >> > basic
    >> > and
    >> > simple but I do not justify the thoroughness of your proposal. I just
    >> > want
    >> > to
    >> > use the 'after update' function of the source field on the main form to
    >> > copy
    >> > the contents (eg a date) into a new record (memo type field) on the
    >> > subform.
    >> > I also wish to do the same thing with a text field to add a note. In
    >> > other
    >> > words I want to replicate a copy and paste function but do not have the
    >> > knowledge to write the macro.
    >> > --
    >> > dtw
    >> >
    >> >
    >> > "Allen Browne" wrote:
    >> >
    >> >> You will need to use the events of the main form to append the new
    >> >> record
    >> >> to
    >> >> the subform (or to its table.)
    >> >>
    >> >> Since you want to use the OldValue (the value before it changed), you
    >> >> will
    >> >> need to use the BeforeUpdate event of the form to get the values.
    >> >> However,
    >> >> in that event, you don't know if the write will succeed, so the
    >> >> logging
    >> >> must
    >> >> take place in Form_AfterUpdate. It therefore requires both events.
    >> >>
    >> >> If you need to log deletions as well, things get even more
    >> >> interesting.
    >> >> There is a set of routines you can use and an explanation of how to
    >> >> set
    >> >> up
    >> >> the logging tables in this article:
    >> >> Audit Trail - Log changes at the record level
    >> >> at:
    >> >> http://allenbrowne.com/AppAudit.html
    >> >>
    >> >> "wilcoit" <wilcoit@discussions.microsoft.com> wrote in message
    >> >> news:287CD2DC-76CE-45A9-9DFC-48EB44DFC5C3@microsoft.com...
    >> >> >I want to copy data from a control on a form (primary table) to a
    >> >> >control
    >> >> >on
    >> >> > subform (secondary table)- one to many relationship. The purpose is
    >> >> > to
    >> >> > create
    >> >> > a history of the business process by recording critical data via the
    >> >> > subform.
    >> >> > I need to create a new record via the subform every time data on the
    >> >> > main
    >> >> > form
    >> >> > is changed or added.
    >> >> > --
    >> >> > dtw
     

Share This Page