Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removal of confirmation message (2003)

    For some reason when l have unchecked (i.e not ticked) the

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Removal of confirmation message (2003)

    Try unticking "Confirm document deletions".

    The message you have is about the deletion of a table, not the running of an action query. An action query affects records in tables, or makes new tables.
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    I tried this but still it prompted me for confirmation message, any other suggestions as to how l could eradicate the confirmation message?

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Removal of confirmation message (2003)

    I have just tested this and see what the problem is.

    You are running a make table query, which wants to recreate a table that already exists.

    The only way I could get rid of the message was to explicitly delete the table myself before running the query.

    DoCmd.DeleteObject acTable, strtablename
    DoCmd.OpenQuery "strQueryName", acViewNormal
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    I have modified you coding with the querties and tables l am using, see below:

    table is: tbl AVTS_final_output
    query is: qry create tbl AVTS final output


    DoCmd.DeleteObject acTable strtbl AVTS_final_output DoCmd.OpenQuery "str qry create tbl AVTS final output", acviewNormal


    I now have a few questions:


    1. Will the above line of coding work, (i.e is it valid?)

    2. Is this coding called from a macro , if so how do l put it in the macro?
    Or do l call it from a command button, if l call it from a command button where does it go in the coding?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Removal of confirmation message (2003)

    <hr>DoCmd.DeleteObject acTable strtbl AVTS_final_output DoCmd.OpenQuery "str qry create tbl AVTS final output", acviewNormal<hr>
    This needs to be two separate lines
    For the names of the two things referred to (table and query) you either assign those names to variables, or include the name directly in the commnad with " " marks around them. So I suggest:


    DoCmd.DeleteObject acTable "tbl AVTS_final_output"
    DoCmd.OpenQuery "qry create tbl AVTS final output", acviewNormal


    This looks OK to me.

    To use this code, create a command button. (if the command button wizard starts to run click cancel.)
    Display the properties sheet and give the button a caption (the word you see on the button) and find the Click Event.

    On the properties sheet click the three dots that appear when you click your mouse in the On Click line.
    this will take you to the VBA Editor.

    Put the after the the line that starts

    Private Sub....
    put it here

    End Sub
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    I tried this but got an error, see attachment.
    Any ideas as to the cause of this error?

  8. #8
    Star Lounger
    Join Date
    Nov 2005
    Location
    Edmonton, Alberta, Canada
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    Try putting a comma after acTable

  9. #9
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    I still appear to be getting an error message. Attached is the database l am using and the error l am getting.

    Any help is appreciated. If you click on the button

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Removal of confirmation message (2003)

    The openquery is bombing out, the query name is incorrect.

  11. #11
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    That works great , thanks.
    What l now require is how to integrate the coding so that it is combined into the

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Removal of confirmation message (2003)

    I'm feeling lazy this morning. where do you wish to put the 2 lines of code? Before or after the RunMacro code?

    sorry about this but GET RID OF MACROS !!!!!

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Removal of confirmation message (2003)

    You can convert the existing macro to VBA , then paste the new code in before the existing two lines.
    .At the database window, go to Macros, select your macro then go to Tools..Macros..convert Macros to VB
    When it is finished you will find a mOdule called "converted Macro.."

    Copy this code from it:
    DoCmd.TransferText acImportDelim, "", "tbl AVTS_data", "Cocuments and SettingsJRennDesktopAccess stuffAVTS.txt", False, ""
    DoCmd.OpenQuery "qry remove duplicates tbl AVTS", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry remove duplicates tbl AVTS"
    DoCmd.OpenQuery "qry create tbl AVTS no duplicates", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry create tbl AVTS no duplicates"

    and paste it into your existing code where you want it. I thinjk it goes before the existing lines, but I am not sure.
    Regards
    John



  14. #14
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of confirmation message (2003)

    The code appears to be working fine , but for some reason the message is still appearing which l want to remove by using this code, see attachment.
    It therefore appears there is something wrong with the coding, any help would be appreciated.


    Private Sub Command40_Click()
    DoCmd.TransferText acImportDelim, "", "tbl AVTS_data", "Cocuments and SettingsJBloggsDesktopAccess stufftestdata.txt", False, ""
    DoCmd.OpenQuery "qry remove duplicates tbl AVTS", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry remove duplicates tbl AVTS"
    DoCmd.OpenQuery "qry create tbl AVTS_data_no_duplicates", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry create tbl AVTS_data_no_duplicates"
    DoCmd.DeleteObject acTable, "tbl AVTS_data_no_duplicates"
    DoCmd.OpenQuery "qry create tbl AVTS_no_duplicates", acViewNormal
    End Sub

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Removal of confirmation message (2003)

    Enclose the code you have like:
    DoCmd.SetWarnings False

    DoCmd.OpenQuery "qry remove duplicates tbl AVTS", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry remove duplicates tbl AVTS"
    DoCmd.OpenQuery "qry create tbl AVTS_data_no_duplicates", acViewNormal, acEdit
    DoCmd.Close acQuery, "qry create tbl AVTS_data_no_duplicates"
    DoCmd.DeleteObject acTable, "tbl AVTS_data_no_duplicates"
    DoCmd.OpenQuery "qry create tbl AVTS_no_duplicates", acViewNormal

    DoCmd.SetWarnings True

Page 1 of 2 12 LastLast

Posting Permissions

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