Welcome to SPN

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

Sign Up Now!

Move records

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

Tags:
  1. pjy

    pjy
    Expand Collapse
    Guest

    I wonder if anyone can help...I have a form which displays records of
    services to a specific user. occasionally 1 or more of these services\records
    needs to be removed..is there any way I can move these records to another
    table as I need to then print the records I have removed. I would need to use
    the form to display and remove the records via a button if that's
    possible..thanks for your help...

    pat
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    To move them to another table, you need to execute 2 action queries:
    - an INSERT to add them to the other table, and
    - a DELETE to remove them from the current table.

    To get an all-or-nothing result, you will want to wrap these 2 operations in
    a transaction. Details in:
    Archive: Move records to another table
    at:
    http://allenbrowne.com/ser-37.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.

    "pjy" <pjy@discussions.microsoft.com> wrote in message
    news:99CB3784-C9BC-439C-8706-023A3D7F6977@microsoft.com...
    >I wonder if anyone can help...I have a form which displays records of
    > services to a specific user. occasionally 1 or more of these
    > services\records
    > needs to be removed..is there any way I can move these records to another
    > table as I need to then print the records I have removed. I would need to
    > use
    > the form to display and remove the records via a button if that's
    > possible..thanks for your help...
    >
    > pat
     
  4. pjy

    pjy
    Expand Collapse
    Guest

    Hi Allen cheers for that...but it looks far to complicated for what I'm
    trying to do...A user has 10 services against their ID. one of these services
    is no longer required i want to move the record in focus to another table (so
    that it does not exist in table that provides data for viewed form) and then
    print a specific form with this record on it based on the date and user it
    was deleted\moved from. I hope this explains better what i'm trying to do and
    hopefully it's easier to do than the transaction thing.

    Cheers

    "Allen Browne" wrote:

    > To move them to another table, you need to execute 2 action queries:
    > - an INSERT to add them to the other table, and
    > - a DELETE to remove them from the current table.
    >
    > To get an all-or-nothing result, you will want to wrap these 2 operations in
    > a transaction. Details in:
    > Archive: Move records to another table
    > at:
    > http://allenbrowne.com/ser-37.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.
    >
    > "pjy" <pjy@discussions.microsoft.com> wrote in message
    > news:99CB3784-C9BC-439C-8706-023A3D7F6977@microsoft.com...
    > >I wonder if anyone can help...I have a form which displays records of
    > > services to a specific user. occasionally 1 or more of these
    > > services\records
    > > needs to be removed..is there any way I can move these records to another
    > > table as I need to then print the records I have removed. I would need to
    > > use
    > > the form to display and remove the records via a button if that's
    > > possible..thanks for your help...
    > >
    > > pat

    >
    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    You can do it without the transaction.

    The basic code is just this:
    Dim db As DAO.Database
    Dim strSql As String

    strSql = "INSERT INTO ...
    db.Execute strSql, dbFailOnError
    strSql = "DELETE FROM ...
    db.Execute strSql, dbFailOnError

    The specifics are handled by getting the SQL statements right. To do that,
    create a query using any specific value as criteria, change it to an Append
    query (Append on Query menu), and then switch to SQL View (View menu) to see
    an example of what you need to create.

    In the same way, mock up your Delete query to get the string you need for
    that also.

    --
    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.

    "pjy" <pjy@discussions.microsoft.com> wrote in message
    news:D0AA4E49-E818-468C-8563-8523696244AD@microsoft.com...
    > Hi Allen cheers for that...but it looks far to complicated for what I'm
    > trying to do...A user has 10 services against their ID. one of these
    > services
    > is no longer required i want to move the record in focus to another table
    > (so
    > that it does not exist in table that provides data for viewed form) and
    > then
    > print a specific form with this record on it based on the date and user it
    > was deleted\moved from. I hope this explains better what i'm trying to do
    > and
    > hopefully it's easier to do than the transaction thing.
    >
    > Cheers
    >
    > "Allen Browne" wrote:
    >
    >> To move them to another table, you need to execute 2 action queries:
    >> - an INSERT to add them to the other table, and
    >> - a DELETE to remove them from the current table.
    >>
    >> To get an all-or-nothing result, you will want to wrap these 2 operations
    >> in
    >> a transaction. Details in:
    >> Archive: Move records to another table
    >> at:
    >> http://allenbrowne.com/ser-37.html
    >>
    >> "pjy" <pjy@discussions.microsoft.com> wrote in message
    >> news:99CB3784-C9BC-439C-8706-023A3D7F6977@microsoft.com...
    >> >I wonder if anyone can help...I have a form which displays records of
    >> > services to a specific user. occasionally 1 or more of these
    >> > services\records
    >> > needs to be removed..is there any way I can move these records to
    >> > another
    >> > table as I need to then print the records I have removed. I would need
    >> > to
    >> > use
    >> > the form to display and remove the records via a button if that's
    >> > possible..thanks for your help...
     

Share This Page