My code does not work.
-
2012年3月6日 21:51
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 SubThe 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月7日 1:37
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日 8:34回答者:
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日 13:57
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 ManagementI 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日 14:57 NEVERMIND. I recreated everything in a completely new database file and my code magically works again. Stupid trash.
-Nothing to see. Move along.
- 回答の候補に設定 danishaniModerator 2012年3月10日 18:40
- 回答としてマーク danishaniModerator 2012年3月20日 19:00
