Results 1 to 9 of 9
  1. #1
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12

    Macro behavior using set warnings as false and an enter date requirement

    Hi All,



    I have a range of queries that perform Delete, make table, append and updates based on criteria. I created a macro to run them. the macro pops up the confirmation to modify data so i have the macro turn the warnings off at the beginning and turn them back on at the end. my question / confusion is based around the Make table query, it pops up a confirmation that the existing table will be deleted but the default confirmation button is set to No, if i turn of the warnings what will happen here? will it still delete the table and create the new one or do i have to add code to change the confirmation to yes?

    Screen shot of the message added

    the macro also runs 5 queries that require a date to be entered, each month this date is the same, is there a way i can enter the date once and have that used for all the queries?

    Thanks
    Attached Thumbnails Attached Thumbnails msaccess messsgae.JPG  

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    turn OFF the warnings and you wont get the message.

    use a form, with a text box for the date: txtDate
    then all queries use this as criteria:

    forms!fMyForm!txtDate

  3. #3
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Thanks for this, I can turn the warnings off but I'm wondering what will the above delete Make table confirmation box do? If it doesn't appear does access just go ahead and delete the table and create the new one or is it defaulted to the 'no' so the table isn't deleted?

    Great, i'll set up the form now, thanks a mil :-)

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Why not just try it, would take a few seconds?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I question the need to delete a table, then recreate it. Does the structure (fields) change? Why not just delete the RECORDS? Constantly deleting and recreating tables causes dB bloat and could cause corruption.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Steve. Is there some rationale that requires the delete and re- Make Table?
    Perhaps you could describe to us in simple English what you are trying to achieve rather than How you have done something.

  7. #7
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Hi,

    The structure can change which is why its a delete table as the source of the data creation can have either more or less fields (needed for other departments but not mine) than the previous file. Its a new enough file so i would imagine once its in circulation for a few weeks then a structure will be settled on and i can relfect it in the database :-)

  8. #8
    Tia2021 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2021
    Posts
    12
    Hi Orange,

    I've just replied to Steve so hopefully this covers your question too :-)

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the fields never change names then appending from a source to a permanent table is an option as long as you will have all the possible fields in the target table.

    I'm one who has repeated the claim about make table queries and corruption but lately I'm not sure. I've seen commentary to the contrary and it's not in Allen Browne's list of causes. However, some of the things he does mention could, in conjunction with mt queries, be the actual cause and not the query/practice itself. Name Autocorrect might be one of those. If you stick with the mt route, make sure you review the link and make frequent backups of your be.

    http://allenbrowne.com/ser-25.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-01-2017, 10:28 AM
  2. set warnings false
    By msmithtlh in forum Macros
    Replies: 3
    Last Post: 06-10-2016, 01:15 PM
  3. Enter Key Behavior
    By jtm013 in forum Forms
    Replies: 8
    Last Post: 12-22-2014, 02:27 PM
  4. Macro - how to turn off warnings for action queries
    By msmithtlh in forum Programming
    Replies: 3
    Last Post: 03-22-2013, 11:00 AM
  5. MS Access Enter Key Behavior
    By RAPSR in forum Programming
    Replies: 0
    Last Post: 12-23-2007, 02:31 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