Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    INSERT INTO, eliminating confirmation prompt (2002, SP2)

    When I use the "INSERT INTO" SQL statement in combination with DoCmd.RunSQL to add a row to a table, for each row I add, I get the confirmation prompt "You are about to append 1 row(s) ... Are you sure you want to do this?"

    How do you get rid of the prompt? I seem to remember you can do it, but I've not been able to find out how to do it. I don't want the database users to have to respond Yes to every row insertion I need to perform.

    I'm using the following INSERT INTO statement:

    SQL1 = "INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES([SampleID],'A');"

    Thanks,
    Jim.

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    My original answer is below. Probably, you can accomplish the same task by just issuing a "docmd.setwarnings = false" before you go into your loop, and "docmd.setwarnings = true" after you're finished.
    thx
    Pat

    You are going to want to set confirm off for action queries. You can set that outside of VB (in your interface) by going to Tools-Options-Edit/Find Tab - Confirm - and click the "Action Queries" radio button. But you won't want to do that for a regular program or for a program you give somone else, and you'll certainly want to turn the action query confirm back on, too.

    There are VB docmds that can set/reset the Tools-Options settings. I'm not sure exactly of the exact phrase to set action queries on and off (it was hard to find this consistently and easily in Access 97, much, much harder in Access XP) _but_ you can find it with a little looking for "constants." Sorry I'm not more helpful, just pointing you in the general direction.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    A simple way to do it that doesn't require changing the SetWarnings setting is to not use DoCmd.RunSQL. Instead do something like this:

    CurrentDB.Execute SQL1
    Charlotte

  4. #4
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    >>CurrentDB.Execute SQL1

    I can't seem to get the syntax (or something else) right...

    I get Run-time error '3601', Too few parameters. Expected 1.


    I found the "Execute Method Example" in help ... it's kind of curious to me. There are two SQL strings in that example ... Change ... and ... Restore.

    For the Change string, this is done:

    strSQLChange = "UPDATE Employees SET Country = " & _
    "'United States' WHERE Country = 'USA'"
    [...]
    Set qdfChange = dbsNorthwind.CreateQueryDef("", _
    strSQLChange)
    [...]
    ExecuteQueryDef qdfChange, rstEmployees


    But for the Restore string, only this is done (which matches the example you gave, i.e., no creating a query def first):

    strSQLRestore = "UPDATE Employees SET Country = " & _
    "'USA' WHERE Country = 'United States'"
    [...]
    dbsNorthwind.Execute strSQLRestore, dbFailOnError


    Do I need to create that query def for this to work? But they don't show a query def for the Restore SQL string, above. Hmmm.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Try this:

    Dim dbsNorthwind As DAO.Database
    Dim strSQLRestore As String

    Set dbsNorthwind = CurrentDb

    strSQLRestore = "UPDATE Employees SET Country = " & _
    "'USA' WHERE Country = 'United States'"
    [...]
    dbsNorthwind.Execute strSQLRestore, dbFailOnError

  6. #6
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    This works (I had to omit the equals sign). I'll use this method for now, since I've not been able to figure out the Execute method.

    Thank you.

  7. #7
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Same thing. I get RTE 3061. "Too few parameters. Expected 1."

  8. #8
    Lounger
    Join Date
    Mar 2001
    Location
    Tampa, Florida, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Here's the exact code I used:

    Set dbsNorthwind = CurrentDb
    SQL1 = "INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES([SampleID],'A');"
    dbsNorthwind.Execute SQL1, dbFailOnError

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Create the query in the query grid. If it works without any errors copy the SQL from the View menu and paste it after SQL1 =

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

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Tell me, what is [SampleID], where does this come from? If it's a control on the form then you will have to use it like:

    SQL1 = "INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES(" & [SampleID] & ",'A');"

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    And, here I was going to come back with further information on "getoption" and "setoption." <g>
    Thanks for the exquisite line, "currentdb.execute sql1." I'll definitely start using that one, rather with dealing with the kludgey (in comparison) "setwarnings" wrapper!
    thx
    Pat

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Well, I've used it successfully for a long time, so, I'm glad you have found it useful! I also am looking at the help files for the execute method (DAO) right now (in my Access 97, which I keep for the Help Files). That just seems so classy. (You can look in the Help Files for "Set Options from Visual Basic," too, if you want to get the full range of options you can set, beyond turning warnings on and off by "setwarnings.")
    thx,
    Pat

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Actually, I usually create a temporary querydef and execute that, since the querydef object has a RecordsAffected property that you can test afterward to see whether the query was successful. In this case, that didn't seem to be necessary.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: INSERT INTO, eliminating confirmation prompt (2002, SP2)

    Thank you, Charlotte!
    Yes, since going into Help to get "edumacated" about the "execute" method, I found & read about that property, too. Recently, I decided I was getting too stuck in my Access ways, I had stopped learning, so have been visiting this forum a little more frequently. I am appreciative of all the things I have learned here!
    thx
    Pat

Posting Permissions

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