none
My code does not work.

    質問

  • Private Sub btn_Submit_Click()
    
        Dim queryStr As String
        Dim i As Integer
        
        i = 1
        
        Do While i <= 50
        
            Select Case i
                Case 1
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q1 & "'"
                Case 2
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q2 & "', " & "''"
                Case 3
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q3 & "', " & "''"
                Case 4
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q4 & "', " & "'" & cmt_q4 & "'"
                Case 5
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q5 & "', " & "'" & cmt_q5 & "'"
                Case 6
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q6 & "', " & "'" & cmt_q6 & "'"
                Case 7
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q7 & "', " & "'" & cmt_q7 & "'"
                Case 8
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q8 & "', " & "'" & cmt_q8 & "'"
                Case 9
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q9 & "', " & "'" & cmt_q9 & "'"
                Case 10
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q10 & "', " & "'" & cmt_q10 & "'"
                Case 11
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q11 & "', " & "'" & cmt_q11 & "'"
                Case 12
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q12 & "', " & "'" & cmt_q12 & "'"
                Case 13
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q13 & "', " & "'" & cmt_q13 & "'"
                Case 14
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q14 & "', " & "'" & cmt_q14 & "'"
                Case 15
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q15 & "', " & "'" & cmt_q15 & "'"
                Case 16
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q16 & "', " & "'" & cmt_q16 & "'"
                Case 17
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q17 & "', " & "'" & cmt_q17 & "'"
                Case 18
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q18 & "', " & "'" & cmt_q18 & "'"
                Case 19
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q19 & "', " & "'" & cmt_q19 & "'"
                Case 20
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q20 & "', " & "'" & cmt_q20 & "'"
                Case 21
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q21 & "', " & "'" & cmt_q21 & "'"
                Case 22
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q22 & "', " & "'" & cmt_q22 & "'"
                Case 23
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q23 & "', " & "'" & cmt_q23 & "'"
                Case 24
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q24 & "', " & "'" & cmt_q24 & "'"
                Case 25
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q25 & "', " & "'" & cmt_q25 & "'"
                Case 26
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q26 & "', " & "'" & cmt_q26 & "'"
                Case 27
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q27 & "', " & "'" & cmt_q27 & "'"
                Case 28
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q28 & "', " & "'" & cmt_q28 & "'"
                Case 29
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q29 & "', " & "'" & cmt_q29 & "'"
                Case 30
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q30 & "', " & "'" & cmt_q30 & "'"
                Case 31
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q31 & "', " & "'" & cmt_q31 & "'"
                Case 32
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q32 & "', " & "'" & cmt_q32 & "'"
                Case 33
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q33 & "', " & "'" & cmt_q33 & "'"
                Case 34
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q34 & "', " & "'" & cmt_q34 & "'"
                Case 35
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q35 & "', " & "'" & cmt_q35 & "'"
                Case 36
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q36 & "', " & "'" & cmt_q36 & "'"
                Case 37
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q37 & "', " & "'" & cmt_q37 & "'"
                Case 38
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q38 & "', " & "'" & cmt_q38 & "'"
                Case 39
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q39 & "', " & "'" & cmt_q39 & "'"
                Case 40
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q40 & "', " & "'" & cmt_q40 & "'"
                Case 41
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q41 & "', " & "'" & cmt_q41 & "'"
                Case 42
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q42 & "', " & "'" & cmt_q42 & "'"
                Case 43
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q43 & "'"
                Case 44
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q44 & "'"
                Case 45
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'" & rsp_q45 & "', " & "'" & cmt_q45 & "'"
                Case 46
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q46 & "'"
                Case 47
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q47 & "'"
                Case 48
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q48 & "'"
                Case 49
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q49 & "'"
                Case 50
                    queryStr = "INSERT INTO [t_User_Ldr_Responses] VALUES(" & _
                        txt_recnum & ", " & i & ", " & "'', " & "'" & cmt_q50 & "'"
            End Select
        
            i = i + 1
            
            queryStr = queryStr & ")"
            
            MsgBox (queryStr)
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL queryStr
         
        Loop
        
        MsgBox "Survey has been added to the database."
        
    End Sub

    The msgbox(querystr) shows a successfully built query.

    It gets to the last line of the subroutine and says the survey has been added.

    Problem is when I look in my table, no data was added. It worked before and something I did caused it to fail all of a sudden!!


    -Nothing to see. Move along.

    2012年3月6日 21:51

回答

すべての返信

  • If you insert:

    debug.print err.description

    after the DoCmd.runsql row does anything show in teh immediate window and if so what?

    As you haven't provided field names, you have to make sure that the numbers added are in the right order and the correct format for teh field types.

    If you run one or more of the INSERT statements in an Access SQL query or the SQL Server Management Studio, do they work there?


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    2012年3月7日 1:37
  • I do not see any connection.

    like: 

    Dim conn As ADODB.Connection
        Set conn = New ADODB.Connection
        conn.Provider = "SQLOLEDB"
        conn.ConnectionString = "Data Source=SERWER;" & _
                "Database=BAZA_DANYCH;uid=UZYTKOWNIK;password=HASLO;"
        conn.Open
        
        If conn.state = adStateOpen Then
        '.... then you add insterts

    Only:

    DoCmd.RunSQL queryStr

    You shoud to add declaration (or show as global)


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    2012年3月7日 8:34
  • If you insert:

    debug.print err.description

    after the DoCmd.runsql row does anything show in teh immediate window and if so what?

    As you haven't provided field names, you have to make sure that the numbers added are in the right order and the correct format for teh field types.

    If you run one or more of the INSERT statements in an Access SQL query or the SQL Server Management Studio, do they work there?


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    I added the line of code you suggested but nothing opens up. FYI the only immediate window I am familiar with is the one in Visual Studio. I'm programming straight out of Access 2007...

    Correct, I didn't provide any field names because I know they are all in order correctly. As I stated, they worked at one point but now they stopped. My query strings have not changed a bit. That's why I don't understand why they don't work anymore.

    I don't have SQ Server Management Studio. So I wouldn't know.


    -Nothing to see. Move along.

    2012年3月7日 13:57
  • NEVERMIND. I recreated everything in a completely new database file and my code magically works again. Stupid trash.

    -Nothing to see. Move along.

    2012年3月7日 14:57