Results 1 to 6 of 6
  1. #1
    Join Date
    May 2005
    Posts
    150

    Unanswered: INSERT INTO... stopped working today? with no change..

    Code:
    Dim i1 As Long
    For i1 = 0 To lst.ListCount - 1
       If lst.Selected(i1) Then
        
        
        stmntAddRef = "INSERT INTO tblStatements ( [Reference#] )"
        stmntAddRef = stmntAddRef & " SELECT Main.[Reference#]"
        stmntAddRef = stmntAddRef & " FROM Main"
        stmntAddRef = stmntAddRef & " WHERE Main.[Reference#]=" & lst.ItemData(i1) & ";"
        
        
        stmntUpdate = "UPDATE tblStatements SET tblStatements.StatementNumber = '" & Forms!frmCreateInvoice!txtStatementNumber & "'"
        stmntUpdate = stmntUpdate & " WHERE (((tblStatements.[Reference#])=" & lst.ItemData(i1) & "));"
      
            
    DoCmd.SetWarnings (False)
      DoCmd.RunSQL stmntAddRef
      DoCmd.RunSQL stmntUpdate
    DoCmd.SetWarnings (True)
    lst.ItemData(i1) returns the correct criteria

    Nothing has changed.. and this has always worked. Does anyone see anything wrong with this?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Kasco
    Code:
    Dim i1 As Long
    For i1 = 0 To lst.ListCount - 1
    If lst.Selected(i1) Then
     
     
    stmntAddRef = "INSERT INTO tblStatements ( [Reference#] )"
    stmntAddRef = stmntAddRef & " SELECT Main.[Reference#]"
    stmntAddRef = stmntAddRef & " FROM Main"
    stmntAddRef = stmntAddRef & " WHERE Main.[Reference#]=" & lst.ItemData(i1) & ";"
     
     
    stmntUpdate = "UPDATE tblStatements SET tblStatements.StatementNumber = '" & Forms!frmCreateInvoice!txtStatementNumber & "'"
    stmntUpdate = stmntUpdate & " WHERE (((tblStatements.[Reference#])=" & lst.ItemData(i1) & "));"
     
     
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL stmntAddRef
    DoCmd.RunSQL stmntUpdate
    DoCmd.SetWarnings (True)
    lst.ItemData(i1) returns the correct criteria

    Nothing has changed.. and this has always worked. Does anyone see anything wrong with this?
    Hi

    Pleas, please get rid of the set warnings - try
    Code:
    CurrentDb.Execute stmntAddRef, dbFailOnError
    instead. Setwarnings does lots more things you really don't want.

    Anyhoo - add a line:

    Code:
    stmntAddRef = "INSERT INTO tblStatements ( [Reference#] )"
    stmntAddRef = stmntAddRef & " SELECT Main.[Reference#]"
    stmntAddRef = stmntAddRef & " FROM Main"
    stmntAddRef = stmntAddRef & " WHERE Main.[Reference#]=" & lst.ItemData(i1) & ";" 
    Debug.Print stmntAddRef
    Your SQL will now be written to the Immediate window. Check it looks about right. Then copy the bit from "SELECT ...." onwards, pop into a query in the QBE, run and see what happens.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2005
    Posts
    150
    Thanks for the quick reply...

    after all the headaches... it turned out that the link to the table had to be refreshed. I don't understand how or why... but all I did was delete the link then reestablish it and now everything works as it did yesterday and months before.

    As far as the Setwarnings section... I am only doing that so the user is not prompted about appending the records. If there is another way around that is better I would definitely use, I am just unaware of a better way.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Kasco
    Thanks for the quick reply...

    after all the headaches... it turned out that the link to the table had to be refreshed. I don't understand how or why... but all I did was delete the link then reestablish it and now everything works as it did yesterday and months before.
    Sounds like the BE schema changed...

    Quote Originally Posted by Kasco
    As far as the Setwarnings section... I am only doing that so the user is not prompted about appending the records. If there is another way around that is better I would definitely use, I am just unaware of a better way
    You can bet your sweet bippy there is:
    Code:
    DoCmd.SetWarnings (False)
    DoCmd.RunSQL stmntAddRef
    DoCmd.RunSQL stmntUpdate
    DoCmd.SetWarnings (True)
    No no no
    Code:
    CurrentDb.Execute stmntAddRef, dbFailOnError
    CurrentDb.Execute stmntUpdate, dbFailOnError
    Yes yes yes

    If you want to avoid DAO you can, I think, use the Application.SetOption method too - but I can't totally remember

    At the minimum ALWAYS include an error trap and ALWAYS make sure you SetWarnings False in that if EVER you decide to use it - it does so much more than suppress insert and update messages. If ever you think "hmm - I wonder why Access isn't prompting me to save when I close stuff?" then check the SetWarnings value.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2004
    Posts
    159
    Yes if that linked object is changed by adding fields to it or ( if it is an excel link) by changing the worksheet then that is what will happen. Thats the first thing to look for when in that situation. I would like to hear more about options to setwarnings also
    Quote Originally Posted by Kasco
    Thanks for the quick reply...

    after all the headaches... it turned out that the link to the table had to be refreshed. I don't understand how or why... but all I did was delete the link then reestablish it and now everything works as it did yesterday and months before.

    As far as the Setwarnings section... I am only doing that so the user is not prompted about appending the records. If there is another way around that is better I would definitely use, I am just unaware of a better way.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by WilliamS
    I would like to hear more about options to setwarnings also
    Ok dokey.

    You can use the CurrentDb.Execute method using the dbFailOnError constant i.e.
    Code:
     
    CurrentDb.Execute "(INSERT INTO A (B) Values ('C')", dbFailOnError
    You get an error message if it fails but no messages of the "You sure you want to do this?" variety.

    Or you can play around with Accesses options:
    Code:
    Application.SetOption "confirm action queries", False
    DoCmd.RunSQL "(INSERT INTO A (B) Values ('C')"	
    Application.SetOption "confirm action queries", True
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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