Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71

    Macros runtime error 2001


    HI, Everyone...
    I've learned this Delete and Append Sql Query from one of my online tutors... am positively sure i tried this vba function before and it worked, now am getting this macros error 2001 and i cant say why on code line
    Code:
    StrSQL = "DELETE tblReturnedDays.* FROM tblReturnedDays;" 
    DoCmd.RunSQL (StrSQL)
    StrSQL = "INSERT INTO tblReturnedDays ( ID, Name, OffencesName, StartDate )" _  
    DoCmd.RunSQL (StrSQL)
    My reasons for posting this new thread; I've tried the trust center settings and its not working either....
    the idea behind this sql is to delete the table then append it simultaneously when the macros is double click on .... However when i click the macros it ask if i want to delete the records in table when i say no i get this debug error... when i click ok the records delete without appending back into the Table.... this function of Delete Append should happen in lighting speed but i get this error cant say why ...Any Suggestions ?

    p.s Not the Full Code
    Last edited by Jamesy_007; 12-13-2020 at 04:45 PM.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    Why isn't DoCmd on its own line?

    Have to use two DoCmd lines. DELETE and INSERT must be run separately.

    Use CurrentDb.Execute instead of DoCmd.RunSQL and won't get popup warning. Also, if action fails there will not be any notification.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71
    Quote Originally Posted by June7 View Post
    Why isn't DoCmd on its own line?

    Have to use two DoCmd lines. DELETE and INSERT must be run separately.

    Use CurrentDb.Execute instead of DoCmd.RunSQL and won't get popup warning. Also, if action fails there will not be any notification.
    Some how, this test macro i pull as a RunCode is nothing working properly even tough i tried this function before it would jus delete and append the table quickly in a blink of an eye ... now its asking if i want to delete and if i click ok... it deletes the table info completely without Appending.. Shows break by where i highlighted [Red]

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    What is the exact error message?

    Where is rest of the INSERT sql statement?

    Is ID an autonumber field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71
    Quote Originally Posted by June7 View Post
    What is the exact error message?

    Where is rest of the INSERT sql statement?

    Is ID an autonumber field?
    Hi June7,
    Am positively sure i ran this function already and it worked fine. Cant say why am getting this error now on all my db and test platforms... ID is an AutoNumber
    i gave part of the sql function.. because the break comes when i test the Marcos RunCode to call the tblReturnedDays function i debugged everything... codes good except when running the macros its Deleting the records but not inserting them back in.... i really don't know why its doing this now... deleted macros and created another ..same problem

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,712
    You missed this:

    Quote Originally Posted by June7 View Post
    Where is rest of the INSERT sql statement?
    As is, your insert SQL is incomplete. It would either be:

    INSET INTO...
    VALUES(...)

    or

    INSET INTO...
    SELECT...FROM...

    As you have it, Access has no idea what to insert.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71
    Quote Originally Posted by pbaldy View Post
    You missed this:
    As is, your insert SQL is incomplete. It would either be:
    INSET INTO...
    VALUES(...)
    or
    INSET INTO...
    SELECT...FROM...
    As you have it, Access has no idea what to insert.
    I know this thread is literally two; i posted twice same day on Forum trying to achieve two different objectives from the same code the code checks the difference between dates and Returns a value while deleting and appending the table at the same time I worked this code before. in another thread [table design datediff two date fields rows_down consecutively] am trying to do the same thing but with two date fields and not one as this thread.... However the issue in this thread is the runtime error in macros this is the code....
    Code:
    Function tblReturnedDays()  
       Dim db As Database, rst As Recordset
       Dim StrSQL As String
       Dim PrevID As Long
       Dim PrevDate As Date
       StrSQL = "DELETE tblReturnedDays.* FROM tblReturnedDays;"
       DoCmd.RunSQL (StrSQL)
       StrSQL = "INSERT INTO tblReturnedDays ( Inm_ID, Name, OffencesName, EntryDate )" _
       & "  SELECT tblInmates.Inm_ID, [LastName] & ', ' & [FirstName] AS Name, tblOffences.OffencesName, [tblInmOff Jun].EntryDate" _
       & " FROM tblOffences INNER JOIN (tblInmates INNER JOIN [tblInmOff Jun] ON tblInmates.Inm_ID = [tblInmOff Jun].Inm_pd) ON tblOffences.Off_ID = [tblInmOff Jun].Off_pd " _
       & " ORDER BY tblInmates.Inm_ID, [tblInmOff Jun].EntryDate;"
        DoCmd.RunSQL (StrSQL)
    Set db = CurrentDb
     Set rst = db.OpenRecordset("tblReturnedDays")
      rst.MoveFirst
    Do
    PrevID = rst!Inm_ID
    PrevDate = rst!EntryDate
    rst.MoveNext
    If rst.EOF Then
      Exit Do
      End If
      If rst!Inm_ID = PrevID Then
      rst.Edit
      rst!DaysReturned = rst!EntryDate
      rst.Update
     End If
    Loop
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
    
    End Function
    Last edited by Jamesy_007; 12-15-2020 at 04:09 AM.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,712
    You mention macro but what you've posted is VBA code. What is the text of the error? See if this helps:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    Don't see anything wrong with SQL syntax.

    So Inm_ID is not an autonumber field - it is foreign key number field?

    Why save first and last names together in one field? If saving Inm_ID why save inmate name into table?

    Why does table name have Jun in it? Is that for the month of June?

    Why would DaysReturned and EntryDate have same value?

    I think you will have to provide db for us to debug code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71
    Quote Originally Posted by pbaldy View Post
    You mention macro but what you've posted is VBA code. What is the text of the error? See if this helps:

    http://www.baldyweb.com/ImmediateWindow.htm
    HI pbaldy... yes there's a macros called RunCode in which you can connect the function you built and runs the code. this macros i named test.. will Delete & Append the table tied to the function in which the sql statement is apart of ... when i click the macros i named test, a message box ask if i want to delete if i click yes it deletes the table without Appending back in the information if i say no i get the error on
    Docmd. RunSQL (StrSQL)

  11. #11
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71
    Quote Originally Posted by June7 View Post
    Don't see anything wrong with SQL syntax.

    So Inm_ID is not an autonumber field - it is foreign key number field?

    Why save first and last names together in one field? If saving Inm_ID why save inmate name into table?

    Why does table name have Jun in it? Is that for the month of June?

    Why would DaysReturned and EntryDate have same value?

    I think you will have to provide db for us to debug code.
    June
    Please Forgive me am new to Ms Access and especially vba now really excited about writing codes and seeing that functionality... so i usually run test pieces and vet alot of codes including ones i develop myself
    sometimes..... i vet and post codes because i realize every [developer has their own unique naming conventions] . tried looking at other threads and db and being honest its sometimes confusing trying to understand another developers db and naming conventions ....so all that said!... Inm_ID is an AutoField I learned this technique with jus the AutoNumber Field and the Date field... but i put in additional fields for my own uniqueness that's why you get INNERJOIN in the sql statement. This function gives the difference between dates in the same row or in commerce db difference between Orders [in my case its not a commerce db its the last time you commit a Crime or Offence]
    Again i worked this and it worked, can't say what am doing wrong now ???

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    Do not set value of an autonumber field with INSERT. A value will be created by table when a record is entered.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,289
    Quote Originally Posted by Jamesy_007 View Post
    .... there's a macros called RunCode in which you can connect the function you built and runs the code. this macros i named test.. will Delete & Append the table tied to the function in which the sql statement is apart of ... when i click the macros i named test, a message box ask if i want to delete if i click yes it deletes the table without Appending back in the information if i say no i get the error on
    Docmd. RunSQL (StrSQL)
    From what you say, you have a Macro (TEST) the First DELETES the table "tblReturnedDays",
    then executes a Function that
    1) tries to DELETE ALL of the records in the table "tblReturnedDays" (a table that does not exist!) and then
    2) recreates the table "tblReturnedDays" again and appends some records.


    Maybe I don't understand ...



    ----------------------------------------------------------------------------------------------------

    Be aware that "Name" is a reserved word in Access.

    "Function tblReturnedDays() " is a poor name IMO. I would use Function "fnReturnedDays()" or "Function "fReturnedDays()"
    Just say'n....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  14. #14
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    71
    Quote Originally Posted by ssanfu View Post
    From what you say, you have a Macro (TEST) the First DELETES the table "tblReturnedDays",
    then executes a Function that
    1) tries to DELETE ALL of the records in the table "tblReturnedDays" (a table that does not exist!) and then
    2) recreates the table "tblReturnedDays" again and appends some records.
    Yep you sort of understand all day trying to debug this thing but the tblReturnedDays is a make-table query where i append back into but using the sql in vba.....After making the Table query with relevant info i create a Number field called days_between that captures the difference between dates in EntryDate Field


    Quote Originally Posted by ssanfu View Post
    Maybe I don't understand ...

    Be aware that "Name" is a reserved word in Access.
    Also Figure out Name is a Reserved word today how i missed this i dont know....but like i keep saying am sure i ran this code as is and it work

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,035
    An alternative to repeatedly deleting and creating table is table is permanent (in frontend of split database) and records are deleted/appended.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 2001 on Switchboard Open
    By mib1019 in forum Forms
    Replies: 1
    Last Post: 06-16-2020, 03:07 PM
  2. Replies: 16
    Last Post: 04-22-2019, 03:56 PM
  3. Replies: 3
    Last Post: 11-21-2015, 07:02 PM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Run-time error 2001
    By RONY in forum Programming
    Replies: 0
    Last Post: 12-23-2008, 06:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums