Results 1 to 4 of 4
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    validate append query has worked before running delete query

    I want to run an append query to move records to another table. IF that is successfull I want it to then run a delete query to remove those records from the first table.



    I dont need any super complex error checking, just a check that i appended X number of records and that I am about to delete the same X number of records.

    I have created the append and delete queries and they are using criteria from a form element to select the records.

    Do I need to run some kind of count and set a variable equal to that and then check it?

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You can use DCOUNT to count of number of records before and after they are added to your table.
    You may also want make a SELECT query version of your DELETE query just to count those records to compare them.
    All these checks would probably need to be done in VBA.

    Question though. Why are you moving records from one table to another? Many times it is not necessary to do that. You can often add a "Flag" field to your table to indicate if certain records need to be categorized differently.

  3. #3
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486
    I want to move them because i want a "Working" table. I do several different things to process line items and once completed I am moving them to a completed reporting table. This allows me to keep the table of outstanding items smaller in case I need to do some manual work with the table. Also, my eventual goal is to put the completed records in SQL Database and it is easier to make quick changes etc if the working table is still in Access.

    I will start with the DCOUNT and the select query first and get back with any questions.
    Thanks

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    If the issue is that you have a "temporary" or "intermediate" table that the records pass-through before being loaded into the "Production" table, then that makes sense. What you usually want to avoid is having multiple production tables of the same (or similar) structure. If you find yourself having to go to two separate tables when you need to search for a particular value, that usually is an indication of a structure that isn't Normalized and could make things unnecessarily difficult for you.

    If you need help getting your VBA started, one trick is to create a macro that runs your queries (Append Query, then Delete Query) and maybe a few other commands to suppress the warnings, if you like (SetWarnings). Then, from the Macro menu, you can elect to convert that macro to Visual Basic. This will give you a good starting point, and then you can insert your DCOUNT calculations, IF...THEN statements, and Message Boxes to complete your code.

    If you run into any issues with that, feel free to post back to this thread.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-06-2015, 04:29 AM
  2. Replies: 6
    Last Post: 12-03-2014, 10:28 PM
  3. running append query without showing some alertes
    By afshin in forum Programming
    Replies: 2
    Last Post: 11-10-2012, 10:49 AM
  4. #Deleted issue with Delete/Append Query
    By WendyCha in forum Access
    Replies: 4
    Last Post: 08-05-2011, 08:34 AM
  5. Running a Delete (Records) Query on Close
    By NoiCe in forum Queries
    Replies: 1
    Last Post: 07-12-2009, 06:17 PM

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