locked
VBA code will not continue after running DoCmd.DeleteObject command. RRS feed

  • Question

  • I am attempting to create code that will first delete a query in the database and after it creates it do do additional items. But the code automatically stops after it deletes the query (it will not even run a message command).

    Anyone have any ideas why it is doing this?

    Tuesday, January 7, 2020 6:55 PM

Answers

  • You're not using my code.  Please copy it verbatim and try it.

    Also debug your SQL statement to ensure it is valid.

    Lastly ensure your code compiles without errors, because your original code does not.  Until you address that there is no point looking any further.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by TWStadley Wednesday, January 8, 2020 5:11 PM
    Tuesday, January 7, 2020 9:52 PM

All replies

  • Can you post your code or a sample db for us to review.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Tuesday, January 7, 2020 7:25 PM
  • Here is the code.

                            

    Private Sub cmd_Character_Creation_Click()
        'Create Variables
        Dim stg_SQLView1 As String
        Dim stg_SQLView2 As String
        Dim stg_SQLView3 As String
        Dim stg_SQLView4 As String
        Dim stg_sqlview_Full As String

        'Set the variable to the Game Name
        g_stg_GameName = lst_Game.Value

        'Set the variables for the SQL code
        stg_SQLView1 = "SELECT tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game,"
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.DEX, tbl_Stats_Character.SPD, tbl_Stats_Character.CON, "
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.REC, tbl_Stats_Character.END, tbl_Stats_Character.BODY, tbl_Stats_Character.STUN"
        stg_SQLView2 = "FROM tbl_Stats_Character"
        stg_SQLView3 = "WHERE (((tbl_Stats_Character.Game)=" & """" & g_stg_GameName & """" & "))"
        stg_SQLView4 = "ORDER BY tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game;"

        'Put the lines together vbCrLf
        stg_sqlview_Full = stg_SQLView1 & vbCrLf & stg_SQLView2 & vbCrLf & stg_SQLView3 & vbCrLf & stg_SQLView4

        'Delete Query
        DoCmd.DeleteObject acQuery, "qry_Input_Character_Stats"

        'Create new Query
        'CreateQueryDef "qry_Input_Character_Stats", stg_sqlview_Full

        MsgBox stg_sqlview_Full, vbOKOnly, "Code"
    End Sub

    Now when I run this it will first show the message box and then delete the query. If I set it to do the Create QueryDef command, it does nothing (it will not delete the query, it will not show the message, and of course will not create a new query).


    • Edited by TWStadley Tuesday, January 7, 2020 8:10 PM
    Tuesday, January 7, 2020 8:05 PM
  • Try something more along the lines of

    Private Sub cmd_Character_Creation_Click()
        'Create Variables
        Dim stg_SQLView1 As String
        Dim stg_SQLView2 As String
        Dim stg_SQLView3 As String
        Dim stg_SQLView4 As String
        Dim stg_sqlview_Full As String
        Const sQuery = "qry_Input_Character_Stats"
    
    '    Set the variable to the Game Name
        g_stg_GameName = lst_Game.Value
    
        'Set the variables for the SQL code
        stg_SQLView1 = "SELECT tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game,"
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.DEX, tbl_Stats_Character.SPD, tbl_Stats_Character.CON, "
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.REC, tbl_Stats_Character.END, tbl_Stats_Character.BODY, tbl_Stats_Character.STUN"
        stg_SQLView2 = "FROM tbl_Stats_Character"
        stg_SQLView3 = "WHERE (((tbl_Stats_Character.Game)=" & """" & g_stg_GameName & """" & "))"
        stg_SQLView4 = "ORDER BY tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game;"
    
        'Put the lines together vbCrLf
        stg_sqlview_Full = stg_SQLView1 & vbCrLf & stg_SQLView2 & vbCrLf & stg_SQLView3 & vbCrLf & stg_SQLView4
    
        'Delete Query if it is found
        If Not IsNull(DLookup("ID", "MSysObjects", "(Name = '" & sQuery & "') AND (Type = 5)")) Then
            DoCmd.DeleteObject acQuery, sQuery
            DoEvents
        End If
        
        'Create new Query
        CurrentDb.CreateQueryDef sQuery, stg_sqlview_Full
        Application.RefreshDatabaseWindow 'Refresh the db to reflect any changes
    
        MsgBox stg_sqlview_Full, vbOKOnly, "Code"
    End Sub


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Tuesday, January 7, 2020 9:08 PM
  • No result, it does the same thing. It will do nothing. It will not create a new query. In fact it I allow it to run the following command:

    CreateQueryDef "qry_Input_Character_Stats", stg_SQLView_Full

    None of the code will run at all (query is not deleted, message box is not shown).

    It will only delete the query and run the message if I get rid of the CreateQueryDef code.


    Tuesday, January 7, 2020 9:17 PM
  • You missed the change Daniel made.

    CurrentDb.CreateQueryDef "qry_Input_Character_Stats", stg_sqlview_Full


    Bill Mosca

    https://groups.io/g/MSAccessProfessionals

    Tuesday, January 7, 2020 9:19 PM
  • If I run this code it will delete the query and will show the message properly (giving the sql for the new query I want to create):

    Private Sub cmd_Character_Creation_Click()
        'Create Variables
        Dim stg_SQLView1 As String
        Dim stg_SQLView2 As String
        Dim stg_SQLView3 As String
        Dim stg_SQLView4 As String
        Dim stg_SQLView_Full As String
        
        'Set the variable to the Game Name
        g_stg_GameName = lst_Game.Value
        
        'Set the variables for the SQL code
        stg_SQLView1 = "SELECT tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game,"
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.DEX, tbl_Stats_Character.SPD, tbl_Stats_Character.CON, "
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.REC, tbl_Stats_Character.END, tbl_Stats_Character.BODY, tbl_Stats_Character.STUN"
        stg_SQLView2 = "FROM tbl_Stats_Character"
        stg_SQLView3 = "WHERE (((tbl_Stats_Character.Game)=" & """" & g_stg_GameName & """" & "))"
        stg_SQLView4 = "ORDER BY tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game;"
        
        'Put the lines together
        stg_SQLView_Full = stg_SQLView1 & vbCrLf & stg_SQLView2 & vbCrLf & stg_SQLView3 & vbCrLf & stg_SQLView4
        
        'Delete Query
        DoCmd.DeleteObject acQuery, "qry_Input_Character_Stats"
        
        'Create new Query
        CreateQueryDef "qry_Input_Character_Stats", stg_SQLView_Full
        
        MsgBox stg_SQLView_Full, vbOKOnly, "Code"
        
    End Sub 'cmd_Character_Creation_Click()


    But if I add the code CreateQueryDef "qry_Input_Character_Stats", stg_SQLView_Full after the code to delete the query, it will do nothing, not delete the query, not show the message, and not create a new query. Does CreateQueryDef not work with Access 2016?

    Oh, one more thing, Thank you very much for your help. I really do appreciate it.

    • Edited by TWStadley Tuesday, January 7, 2020 9:30 PM
    Tuesday, January 7, 2020 9:29 PM
  • It's not doing anything because it can't compile. Again, it should be 

    CurrentDb.CreateQueryDef "qry_Input_Character_Stats", stg_sqlview_Full


    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals


    Tuesday, January 7, 2020 9:34 PM
  • Here is the code.

                            

    Private Sub cmd_Character_Creation_Click()
        'Create Variables
        Dim stg_SQLView1 As String
        Dim stg_SQLView2 As String
        Dim stg_SQLView3 As String
        stg_SQLView1 = "SELECT tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game,"
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.DEX, tbl_Stats_Character.SPD, tbl_Stats_Character.CON, "
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.REC, tbl_Stats_Character.END, tbl_Stats_Character.BODY, tbl_Stats_Character.STUN"
        stg_SQLView2 = "FROM tbl_Stats_Character"
        stg_SQLView3 = "WHERE (((tbl_Stats_Character.Game)=" & """" & g_stg_GameName & """" & "))"
        stg_SQLView4 = "ORDER BY tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game;"

        'Put the lines together vbCrLf
        stg_sqlview_Full = stg_SQLView1 & vbCrLf & stg_SQLView2 & vbCrLf & stg_SQLView3 & vbCrLf & stg_SQLView4

    Hi TWStadley,

    In your code you fill stg_SQLView2, stg_SQLView3, etc., and then glue them together with only a vbCrLf inbetween. You also need a separating space.

    YOu can best place this space IN FRONT of each line for easy overview, like

        stg_sqlView_Full = "SELECT tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game" _
                                 & ", tbl_Stats_Character.DEX, tbl_Stats_Character.SPD, tbl_Stats_Character.CON" _
                                 & ", tbl_Stats_Character.REC, tbl_Stats_Character.END, tbl_Stats_Character.BODY, tbl_Stats_Character.STUN" _
                                 & " FROM tbl_Stats_Character" _
                                 & " WHERE (((tbl_Stats_Character.Game)=" & """" & g_stg_GameName & """" & "))" _
                                 & " ORDER BY tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game"

    Next question, why do you delete the QueryDef and create a new one? Is that because you need other fields, or have different selection criteria?

    You can just create your dynamical stg_sqlView_Full in code, adjusted for the right conditions, and use this sql_string to open a recordset.

    Imb.

    Tuesday, January 7, 2020 9:36 PM
  • You're not using my code.  Please copy it verbatim and try it.

    Also debug your SQL statement to ensure it is valid.

    Lastly ensure your code compiles without errors, because your original code does not.  Until you address that there is no point looking any further.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by TWStadley Wednesday, January 8, 2020 5:11 PM
    Tuesday, January 7, 2020 9:52 PM
  • Hi Daniel,

    Your SQL syntax is without spaces between key words. I'm not sure "vbCrLf" will create spaces when you concatenate the SQL strings.

    From my experience, "vbCrLf" will not create a space or spaces within the SQL syntax. I have never been successful with it. No luck with that.


    • Edited by AccessVandal Wednesday, January 8, 2020 2:45 AM typo
    Wednesday, January 8, 2020 2:43 AM
  • You may be right, I didn't review the SQL at all.  I was concentrating on the VBA.  This was also why I mentioned debugging the SQL statement to ensure it was valid.

    You are quite correct that VbCrLf will not atd spaces.

    So the resulting correction should be

    Private Sub cmd_Character_Creation_Click()
        'Create Variables
        Dim stg_SQLView1          As String
        Dim stg_SQLView2          As String
        Dim stg_SQLView3          As String
        Dim stg_SQLView4          As String
        Dim stg_sqlview_Full      As String
        Const sQuery = "qry_Input_Character_Stats"
    
        '    Set the variable to the Game Name
        On Error GoTo Error_Handler
    
        g_stg_GameName = lst_Game.Value
    
        'Set the variables for the SQL code
        stg_SQLView1 = "SELECT tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game, "
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.DEX, tbl_Stats_Character.SPD, tbl_Stats_Character.CON, "
        stg_SQLView1 = stg_SQLView1 & "tbl_Stats_Character.REC, tbl_Stats_Character.END, tbl_Stats_Character.BODY, tbl_Stats_Character.STUN"
        stg_SQLView2 = " FROM tbl_Stats_Character"
        stg_SQLView3 = " WHERE (tbl_Stats_Character.Game='" & g_stg_GameName & "')"
        stg_SQLView4 = " ORDER BY tbl_Stats_Character.Player, tbl_Stats_Character.Character, tbl_Stats_Character.Game;"
    
        'Put the lines together vbCrLf
        stg_sqlview_Full = stg_SQLView1 & vbCrLf & stg_SQLView2 & vbCrLf & stg_SQLView3 & vbCrLf & stg_SQLView4
    
        'Delete Query
        If Not IsNull(DLookup("ID", "MSysObjects", "(Name = '" & sQuery & "') AND (Type = 5)")) Then
            DoCmd.DeleteObject acQuery, sQuery
            DoEvents
        End If
    
        'Create new Query
        CurrentDb.CreateQueryDef sQuery, stg_sqlview_Full
        Application.RefreshDatabaseWindow
    
        MsgBox stg_sqlview_Full, vbOKOnly, "Code"
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: cmd_Character_Creation_Click" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occurred!"
        Resume Error_Handler_Exit
    End Sub


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, January 8, 2020 10:43 AM
  • I see what I missed and that did the trick. Thank you once again very much!
    Wednesday, January 8, 2020 5:11 PM