My query code doesn't work (1 Viewer)

B

blacksaibot

Registered User.
Local time
Today, 17:06
Joined
Jan 20, 2010
Messages
31
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
 
G

gblack

Registered User.
Local time
Today, 22:06
Joined
Sep 18, 2002
Messages
632
I usually post my SQL to a txt file then cut and paste it back into Access to see if it will run.

Put the code (below) just above the set warnings in your code and it should create a SQL.txt file (to whatever path you set for it) that you can look at...

Open "C:\path to your desktop or wherever\SQL.txt" For Output As #1
Write #1, queryStr
Close #1
Click to expand...
 
T

tehNellie

Registered User.
Local time
Today, 22:06
Joined
Apr 3, 2007
Messages
751
So what happens when you run it, what error message(s) do you get?

There appear to be a number of typos/discrepencies in your SQL at various points (past the obvious :eek:at the structure of it) but without some context past it doesn't work it's hard to suggest more.
 
B

blacksaibot

Registered User.
Local time
Today, 17:06
Joined
Jan 20, 2010
Messages
31
NEVERMIND. I recreated everything in a completely new database file and my code magically works again. Stupid trash.
 
You must log in or register to reply here.

Similar threads

A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
516
ebs17
E
M
insert into code
Replies
2
Views
231
mhakim
M
W
Converting IE access VBA code to work with Edge
Replies
4
Views
1,015
wackywoo105
W
M
Solved Access to Excel link
Replies
8
Views
774
bastanu
bastanu
L
Solved Build Query With Single/Double Quotes Potentially In Free Text Field
Replies
6
Views
480
LGDGlen
L

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom