Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2008
    Posts
    21

    Unanswered: Getting rid of confirmation "You are about to append 1 row" MS Access SQL

    I am inserting new row on the table using the following code

    Dim strSqlUpdate1 As String
    strSqlUpdate1 = "INSERT INTO [Daily Fault Update]([Time Recorded], [Reason and Correction]) VALUES (Now()), 'Fault resolved on ' & [Forms]![TXFaults]![Date_Resolved] & 'Resolution being - ' & [Forms]![TXFaults]![Resolution] )"
    DoCmd.RunSQL strSqlUpdate1

    The code works fine exept this popup message saying "You are about to append 1 row(s). Once you click yes, you can't use the undo command to reverse changes... Are you sure.."

    Is there anyway to disable this message?

  2. #2
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    On the Menu bar select options from here select Edit/Find tab and uncheck the Action queries under the Confirm section.
    This will turn of all warnings of queries throughit your application.

    Alternatively you could use

    DoCmd.Setwarnings False

    before you append code. This will turn of all warning and error messages so would be best to reset it afetr the append code has run with

    DoCmd.SetWarnings True

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you use .SetWarnings, don't forget to use proper error handling constructs to ensure they're turned back on again!
    Code:
    Private Sub somebutton_Click()
    
    On Error GoTo ErrorHandler
    
        DoCmd.SetWarnings False
    
        'your code
    
    ExitHandler:
        DoCmd.SetWarnings True
        Exit Sub
    
    ErrorHandler:
        MsgBox Err.Number & Chr(9) & Err.Description
        Resume ExitHandler
    
    End Sub
    I'm getting good at writing code from memory
    George
    Home | Blog

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Save me looking it up? What's Chr(9)?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If memory serves; Chr(9) is a tab.
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    OK, thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    forget setwarnings and runsql.

    faster, easier, less typing, less scope for issues with "leaky" error trapping, all round a million times better:

    currentdb.execute strSQL
    ooops: in your case
    currentdb.execute strSqlUpdate1


    !done

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Apr 2008
    Posts
    21
    The code works, Thanks very much, the popup has been a snag to my database for weeks especially to users who are non programmers. Suppose s/he presses the NO button to the popup message which will eventually open the debug window - where one edit would mess up everything.

    I appreciate your assistance and, ofcourse your codes-manship.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hope it was mine you tried, not the setwarnings/runsql/setwarnings nonsense.

    you can improve .execute with additional code to check what actually happened, but it takes you into the dark recesses of DAO

    assuming you have a reference set to the DAO library and you are talking to an Access/JET database engine:
    Dim someDB as DAO.database
    Dim yourSQL as string
    yourSQL = "UPDATE blah blah blah"
    Set someDB = currentdb
    someDB.execute yourSQL
    Select Case someDB.recordsaffected
    Case 0
    'nothing happened - what do you want to do about that?
    Case 1
    'one record inserted/updated depending on your SQL - this is probably what you wanted so do nothing
    Case Else
    'more than one record affected - ?? did you trash your database or was it intentional
    End Select

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Apr 2008
    Posts
    21
    Correction!
    The code that worked is georgev's which uses (.SetWarnings).
    With izyrider's code I end up with
    "Runtime error '3061' Too Few Parameters, Expected 4"

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so sad...
    ...another victim of unnecessary DoCmd nonsense.

    if your SQL works with docmd.runsql, it works FASTER and SAFER (and is easier to type) with currentdb.execute

    both methods require a valid SQL action statement - nothing more

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    ^^ Thank goodness for freedom of choice lol
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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