locked
INSERT vba code Access 2010 RRS feed

  • Question

  • Hi

    I am having trouble with part of this code,

    The INSERT code in the loop will not add the NotificationDetailOid to the table, i have tried...

    SendDate is a datestamp.

    I open the recordset which has the NotificationDetailOid, the datatype is a long, if i remove the NotificationDetailOid  out of the string and add  2 or 3 then it will pass it to the table...

    can someone see where i am going wrong

    thanks

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        
        Dim strLinkCriteria As String
        Dim strSQL As String
        Dim SQL As String
        Dim SQLD As String
        Dim sendDate As String
    sendDate = Format(Date, "ddmmyyyy")
    strSQL = "Select * from qryWordMergeHepB Where  qryWordMergeHepB.[Print]= -1"
    DoCmd.SetWarnings False
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    With rs
        Do While Not .EOF
            SQL = "UPDATE qryWordMergeHepB " & "SET Printed = True where [print]= True "
            SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) Values ('" & sendDate & "', NotificationDetailsOid);" 'NotificationDetailsOid = PK datatype LONG
            .MoveNext
        Loop
        .Close
    End With
    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQLD
        Me.Refresh
    DoCmd.SetWarnings True


    David

    Sunday, May 20, 2012 11:46 PM

Answers

  • If I understand correctly what you are attempting, you need to concatenate the value of the
    recordset's NotificationDetailsOid column into the SQL statement:

    SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) " & _
                 "VALUES ('" & sendDate & "'," & .Fields("NotificationDetailsOid") & ")"
                 'NotificationDetailsOid = PK datatype LONG

    Also you would need to execute this SQL statement  within the loop, if, as I assume, it is intended to insert a row into the table for each row in the recordset.

    Ken Sheridan, Stafford, England

    • Marked as answer by KIWI DAVE Monday, May 21, 2012 1:38 AM
    Monday, May 21, 2012 12:05 AM
  • That's exactly what Ken & I tried to tell you: you have to run the SQL inside the loop, not once the loop has completed:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim NID As String
        Dim strLinkCriteria As String
        Dim strSQL As String
        Dim SQL As String
        Dim SQLD As String
        Dim sendDate As String
      sendDate = Format(Date, "ddmmyyyy")
      strSQL = "Select * from qryWordMergeHepB " & _
        "Where    qryWordMergeHepB.[Print]= -1"
      Set db = CurrentDb
      Set rs = db.OpenRecordset(strSQL)
      With rs
        Do While Not .EOF
            
            SQL = "UPDATE qryWordMergeHepB " & _
              "SET Printed = True where [print]= True "
            
            SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) " & _
              "VALUES  ('" & sendDate & "', " & rs!NotificationDetailsOid & ")"
            db.Execute SQL, dbFailOnError
            db.Execute SQLD, dbFailOnError
            .MoveNext
        Loop
        .Close
      End With
        Me.Refresh
    Note that I'm using the database Execute method, rather than RunSQL. See what Allen Browne has at http://www.allenbrowne.com/ser-60.html for some of the reasons why.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by KIWI DAVE Monday, May 21, 2012 1:38 AM
    Monday, May 21, 2012 1:22 AM

All replies

  • SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) Values ('" & sendDate & "', " & NotificationDetailsOid & ");"

    (actually, the semi-colon is optional)

    Actually, hold on. NotificationDetailsOid doesn't seem to be assigned a value anywhere. If it's supposed to be picked up from the recordset, you'd need

    SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) Values ('" & sendDate & "', " & rs!NotificationDetailsOid & ");"

    Of course, you do realize that your code only runs the SQL statements once, after the recordset has been completely navigated. In other words, you'll only get the last value it read.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)



    Sunday, May 20, 2012 11:55 PM
  • If I understand correctly what you are attempting, you need to concatenate the value of the
    recordset's NotificationDetailsOid column into the SQL statement:

    SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) " & _
                 "VALUES ('" & sendDate & "'," & .Fields("NotificationDetailsOid") & ")"
                 'NotificationDetailsOid = PK datatype LONG

    Also you would need to execute this SQL statement  within the loop, if, as I assume, it is intended to insert a row into the table for each row in the recordset.

    Ken Sheridan, Stafford, England

    • Marked as answer by KIWI DAVE Monday, May 21, 2012 1:38 AM
    Monday, May 21, 2012 12:05 AM
  • Hi,

    thanks for all the input

    I am going with the following code, the issues is now that rs!NotificationDetailsOid picks up the different IDs in the loop id 1, 2, 3 and so on, but only applys the first record in the qryWordMergeHepB to the Activity table when it should insert 3 records...

    any ideas?

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim NID As String
        Dim strLinkCriteria As String
        Dim strSQL As String
        Dim SQL As String
        Dim SQLD As String
        Dim sendDate As String
    sendDate = Format(Date, "ddmmyyyy")
    strSQL = "Select * from qryWordMergeHepB Where  qryWordMergeHepB.[Print]= -1"
    DoCmd.SetWarnings False
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    With rs
        Do While Not .EOF
            
            SQL = "UPDATE qryWordMergeHepB " & "SET Printed = True where [print]= True "
            
            SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) " & "VALUES  ('" & sendDate & "', " & rs!NotificationDetailsOid & ")"
            .MoveNext
        Loop
        .Close
    End With
    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQLD
        Me.Refresh
    DoCmd.SetWarnings True


    David

    Monday, May 21, 2012 1:13 AM
  • That's exactly what Ken & I tried to tell you: you have to run the SQL inside the loop, not once the loop has completed:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim NID As String
        Dim strLinkCriteria As String
        Dim strSQL As String
        Dim SQL As String
        Dim SQLD As String
        Dim sendDate As String
      sendDate = Format(Date, "ddmmyyyy")
      strSQL = "Select * from qryWordMergeHepB " & _
        "Where    qryWordMergeHepB.[Print]= -1"
      Set db = CurrentDb
      Set rs = db.OpenRecordset(strSQL)
      With rs
        Do While Not .EOF
            
            SQL = "UPDATE qryWordMergeHepB " & _
              "SET Printed = True where [print]= True "
            
            SQLD = "INSERT into tblActivity (ReportSentDate,NotificationDetailsOid) " & _
              "VALUES  ('" & sendDate & "', " & rs!NotificationDetailsOid & ")"
            db.Execute SQL, dbFailOnError
            db.Execute SQLD, dbFailOnError
            .MoveNext
        Loop
        .Close
      End With
        Me.Refresh
    Note that I'm using the database Execute method, rather than RunSQL. See what Allen Browne has at http://www.allenbrowne.com/ser-60.html for some of the reasons why.


    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)

    • Marked as answer by KIWI DAVE Monday, May 21, 2012 1:38 AM
    Monday, May 21, 2012 1:22 AM
  • HI,

    Yes i am blind as a bat...

    I was running the RunSQL outside the loop...drrrrr...

    Sorry

    thanks so much

    Bad monday for me.

    :)


    David

    Monday, May 21, 2012 1:38 AM
  • KIWI DAVE wrote:

    DoCmd.RunSQL SQL

    The problem with  DoCmd.RunSQL is that it ignores any errors.  Either of
    the following will display any error messages received by the query.  If
    using DAO, use Currentdb.Execute strSQL,dbfailonerror..  For ADO use
    CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
    adCmdText    You can then remove the docmd.setwarnings lines.

    If you're going to use docmd.setwarnings make very sure you put the True
    statement in any error handling code as well.   Otherwise weird things
    may happen later on especially while you are working on the app.  For
    example you will no longer get the "Do you wish to save your changes"
    message if you close an object.  This may mean that unwanted changes,
    deletions or additions will be saved to your MDB.

    Also performance can be significantly different between the two methods.
    One posting stated currentdb.execute took two seconds while docmd.runsql
    took eight seconds.  As always YMMV.

    Tony


    Tony Toews, Microsoft Access MVP
    Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
    Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
    For a convenient utility to keep your users FEs and other files   updated see http://www.autofeupdater.com/

    Wednesday, May 23, 2012 9:31 PM