Results 1 to 9 of 9
  1. #1
    danaspps is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    14

    Macro to run an append query and then a delete query when closing a form

    I have a form with a field named Inactive. It is a Yes/No field. If Yes, I want the record to append to a different table and to be deleted from the original table. The point is to have only active records when I open the form, but I don't want to completely delete the inactive records. I copied the original table (structure only) and named it Inactive. Added the Inactive field to the form. Created an Append Query and a Delete Query. When I run the queries they work as expected. I would like to have the queries run automatically when I close the form.



    I tried creating a Macro using RunSQL but when I copy the SQL View from the queries into the SQL Statement of the macro, it only copies the first line. I then went to the properties for the form and put the macro name next to OnClose, but when I close the form I get Syntax Error in INSERT INTO statement.

    Also, I do not know if I want the transaction set to yes or no.

    As you can tell, I have no experience with Macros and have no clue what to do. I am more than happy to upload my database file if that will help.

    Thank you in advance for any help.


    I have also posted this question here, but there seem to be fewer people on that forum. My apologies if I broke the rules. https://getaccessanswers.com/forum/?topic=527.0

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    In the form event: CLOSE
    put your macro name in the box.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    @ranman - think you've been had - looks like the OP has deleted their post

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    OP was moderated, not sure how anybody saw it to respond.

    Rather than move records around, you can simply base the form on a query that only pulls active records, based on your yes/no field.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    danaspps is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    14

    Form Event

    Quote Originally Posted by ranman256 View Post
    In the form event: CLOSE
    put your macro name in the box.
    I did put it in there, that is when I get the Syntax message.

  6. #6
    danaspps is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    14
    Can you tell me what caused by post to be moderated?
    I don't understand your reply. The form is used to enter the records, then at a latter time some records become inactive, so I want them in a separate table.

  7. #7
    danaspps is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    14
    Quote Originally Posted by danaspps View Post
    I have a form with a field named Inactive. It is a Yes/No field. If Yes, I want the record to append to a different table and to be deleted from the original table. The point is to have only active records when I open the form, but I don't want to completely delete the inactive records. I copied the original table (structure only) and named it Inactive. Added the Inactive field to the form. Created an Append Query and a Delete Query. When I run the queries they work as expected. I would like to have the queries run automatically when I close the form.

    I tried creating a Macro using RunSQL but when I copy the SQL View from the queries into the SQL Statement of the macro, it only copies the first line. I then went to the properties for the form and put the macro name next to OnClose, but when I close the form I get Syntax Error in INSERT INTO statement.
    This was the key, I needed to get the entire SQL from the query SQL View into the Macro SQL Statement not just the first line. Once I did that it worked.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think Paul's approach to this is the way to go. Just base the form on a query that excludes the unwanted records. I can't see any reason to move records around.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by danaspps View Post
    Can you tell me what caused by post to be moderated?
    I don't understand your reply. The form is used to enter the records, then at a latter time some records become inactive, so I want them in a separate table.
    The link is likely why the post was moderated (spam risk).

    What are you going to do when management asks for a report that needs to include records from both tables?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-18-2017, 04:01 AM
  2. Macro to delete and then append
    By bb_ in forum Access
    Replies: 6
    Last Post: 08-07-2017, 01:02 PM
  3. Replies: 3
    Last Post: 03-31-2016, 08:42 AM
  4. Replies: 1
    Last Post: 01-16-2013, 10:58 AM
  5. Replies: 7
    Last Post: 07-14-2012, 01:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums