Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Code issues in Access 2010

    I have a function where I select a table, open it, delete the existing records, and then using several different app queries, repopulate the table with new data from several different area. This table then feeds several other queries. It has worked great through Access97, 2003, & 2007. However, with Ac2010 the code hang on one particular area of the code, the DoCmd.RunCommand acCmdDelete. I have both instances of where this command is used in the code highlighted below, however the code never gets to the second command, as it "hangs" on the first one.

    If I manually do this funtion, opening the table, deleting reccords, closing the table, and then running each app query, it works fine. I am a new user to Ac2010 and have already encountered other "issues" (thank you Microsoft)...the main issue or concern is that if I change my db to work in 2010, I still have users who are on Ac2003 and I need to to still work properly for them.



    DoCmd.Echo False, ""
    ' Open the Please Wait form
    DoCmd.OpenForm "frmPleaseWait", acNormal, "", "", acReadOnly, acNormal
    DoCmd.RepaintObject acForm, "frmPleaseWait"

    ' Open the Audit Findings Table
    DoCmd.OpenTable "tblAuditFindingsSummary", acNormal, acEdit
    ' Select All Records in the Table
    DoCmd.RunCommand acCmdSelectAllRecords
    ' ByPass warnings
    DoCmd.SetWarnings False
    ' Delete All Records
    DoCmd.RunCommand acCmdDelete
    ' Close Audit Findings Table
    DoCmd.Close acTable, "tblAuditFindingsSummary"
    ' Open Nonconformance Findings append query
    DoCmd.OpenQuery "appAuditNonconformances", acNormal, acReadOnly
    ' Open Audit Summary1 append query
    DoCmd.OpenQuery "appAuditNothingFound-System", acNormal, acReadOnly
    ' Open Audit Summary1 append query
    DoCmd.OpenQuery "appAuditSummaryReports1", acNormal, acReadOnly
    ' Open Audit Summary2 append query
    DoCmd.OpenQuery "appAuditSummaryReports2", acNormal, acReadOnly
    ' Open Audit Summary3 append query
    DoCmd.OpenQuery "appAuditSummaryReports3", acNormal, acReadOnly
    ' Open Audit Summary4 append query
    DoCmd.OpenQuery "appAuditSummaryReports4", acNormal, acReadOnly
    ' Open Audit Summary5 append query
    DoCmd.OpenQuery "appAuditSummaryReports5", acNormal, acReadOnly
    ' Open Audit Summary6 append query
    DoCmd.OpenQuery "appAuditSummaryReports6", acNormal, acReadOnly

    'Open the Audit Details Summary Table
    DoCmd.OpenTable "tblAuditDetailsSummary", acNormal, acEdit
    ' Select All Records in the Table
    DoCmd.RunCommand acCmdSelectAllRecords
    ' ByPass warnings
    DoCmd.SetWarnings False
    ' Delete All Records
    DoCmd.RunCommand acCmdDelete
    ' Close Audit Details Summary Table
    DoCmd.Close acTable, "tblAuditDetailsSummary"
    ' Open Nonconformance Findings append query
    DoCmd.OpenQuery "appQryAuditDetails", acNormal, acReadOnly

    'UPDATE AUDITOR INFORMATION

    DoCmd.OpenTable "tblAuditorPartSub", acNormal, acEdit
    ' Select All Records in the Table
    DoCmd.RunCommand acCmdSelectAllRecords
    ' ByPass warnings
    DoCmd.SetWarnings False
    ' Delete All Records
    DoCmd.RunCommand acCmdDelete
    ' Close Auditor Participation History Table
    DoCmd.Close acTable, "tblAuditorPartSub"
    ' Open Lead Auditor append query
    DoCmd.OpenQuery "appQryAuditorLead", acNormal, acReadOnly
    ' Open Auditor 1 append query
    DoCmd.OpenQuery "appQryAuditorPart1", acNormal, acReadOnly
    ' Open Auditor 2 append query
    DoCmd.OpenQuery "appQryAuditorPart2", acNormal, acReadOnly
    ' Open Auditor 3 append query
    DoCmd.OpenQuery "appQryAuditorPart3", acNormal, acReadOnly

    ' Close the Please Wait form
    DoCmd.Close acForm, "frmPleaseWait"
    DoCmd.Maximize
    DoCmd.RunCommand acCmdRecordsGoToNext
    DoCmd.RunCommand acCmdRecordsGoToPrevious
    DoCmd.Echo True, ""

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It seems to me that it would be a lot simpler to replace this:
    Code:
    ' Open the Audit Findings Table
    DoCmd.OpenTable "tblAuditFindingsSummary", acNormal, acEdit
    ' Select All Records in the Table
    DoCmd.RunCommand acCmdSelectAllRecords
    ' ByPass warnings
    DoCmd.SetWarnings False
    ' Delete All Records
    DoCmd.RunCommand acCmdDelete
    ' Close Audit Findings Table
    DoCmd.Close acTable, "tblAuditFindingsSummary"
    with this:
    Code:
    docmd.RunSQL "DELETE FROM tblAuditFindingsSummary"
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Replace all the lines of code you have indicated, or just what I had highlighted in red?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    All the lines I indicated.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    The code now stops and displays each "deleting" warning and prompts me to continue. It then stops and asks me if I want to run each app query. I will continue to use what you gave me and see if I can work through the errors.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Sorry - put back the Docmd.Setwarnings falseLine before the line I suggested.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Got it. I had to add the:

    ' ByPass warnings
    DoCmd.SetWarnings False

    Before the line you suggested. Works great!

    Thanks so much!

Posting Permissions

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