Results 1 to 3 of 3
  1. #1
    blacksaibot is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Jan 2010
    Posts
    34

    Red face My query code doesn't work

    I have no idea why this is not working. This is ALL the code I have for the Form I've created. I didn't provide any field names in the query because I know they are all in order correctly. This code worked at some point. I added a new subroutine and everything stopped working. So I deleted everything I added new and left my one routine only and now that won't even work anymore.

    Code:
    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 & ")"
            
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL queryStr
         
        Loop
        
        MsgBox "Survey has been added to the database."
        
    End Sub


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's the standard method of debugging SQL in VBA:

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

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What do you mean by "does not work"? Does it do anything at all? Does it give you an error message?

    One problem with using DoCmd.RunSQL queryStr is that if it fails it won't always tell you.
    Try using CurrentDB.Execute queryStr, dbFailOnError

    This will give you an error message if there is a problem. If you use an error handler (On Error Go to ...) , you could display the offending SQL in the error processing routine. One thing you could check - does your table allow zero-length strings (Allow Zero Length = Yes)?

    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-08-2011, 11:39 AM
  2. !!!!Urgent!!!! Search code doesn't work!
    By Laetilae in forum Programming
    Replies: 4
    Last Post: 12-13-2010, 10:34 PM
  3. Parameter Query doesn't work
    By Kimmie in forum Access
    Replies: 5
    Last Post: 10-07-2010, 01:30 PM
  4. Query doesn't work the day after
    By sithis876 in forum Queries
    Replies: 1
    Last Post: 07-13-2010, 07:11 AM
  5. Query doesn't work when launched by code
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-24-2009, 09:52 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