Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    Angry Unanswered: Append & Delete Current Record - HELP !!

    Hi there.....

    In simple terms I have created 2 queries:

    The first appends the current record into another database, and the 2nd will delete the record from the source database.

    The problem is that the above just moves all records and the deletes the lot !

    How do I get it to move and delete the current record only on display in form view ??

    Any help appreciated ASAP !!

    Thanks

  2. #2
    Join Date
    Mar 2003
    Posts
    46
    Each of your records should have a unique identifier.

    Make sure that both your append and delete query has a criteria for the unique identifier that states

    [Forms]![YourForm]![UniqueID]


    C

  3. #3
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    More Help....

    Am a complete newbie

    Could you amend the deete query for me please ?!?!?!


    DELETE tbl_names.ID, tbl_names.forename, tbl_names.surname
    FROM tbl_names

    Thanks !

  4. #4
    Join Date
    Mar 2003
    Posts
    46
    DELETE tbl_names.ID, tbl_names.forename, tbl_names.surname
    FROM tbl_names
    WHERE (((tbl_names.ID)=[Forms]![YourForm]![ID]))

    This obviously only works if you are on a Form ('YourForm') and you run the queries based on an event (ie. AfterUpdate or OnClick etc.)

    You will then also need to requery your form to no longer show the deleted record

  5. #5
    Join Date
    Jan 2002
    Location
    Nottingham - UK
    Posts
    113

    One more...........

    Trying to figure out the move statemant based on the same principal

    My effort below !

    INSERT INTO tbl_names ( surname, forename, ID ) IN 'C:\Documents and Settings\drt\Desktop\database test\moved.mdb'
    SELECT tbl_names.surname, tbl_names.forename, tbl_names.ID
    FROM tbl_names;
    WHERE (((tbl_names.ID)=Forms!tbl_names!ID));

  6. #6
    Join Date
    Mar 2003
    Posts
    46
    INSERT INTO tbl_names ( surname, forename, ID ) IN 'C:\Documents and Settings\drt\Desktop\database test\moved.mdb'
    SELECT tbl_names.surname, tbl_names.forename, tbl_names.ID
    FROM tbl_names
    WHERE (((tbl_names.ID)=[Forms]![tbl_names]![ID]));

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by CCC
    INSERT INTO tbl_names ( surname, forename, ID ) IN 'C:\Documents and Settings\drt\Desktop\database test\moved.mdb'
    SELECT tbl_names.surname, tbl_names.forename, tbl_names.ID
    FROM tbl_names
    WHERE (((tbl_names.ID)=[Forms]![tbl_names]![ID]));
    yes watch that semi colon as it means end of statement so put it in the wrong place and it don't work
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •