No Value given for one or more required parameters

[captainlove]

I have a table, tblqcplist, which I have just added a field qcpcomment and a table tbltemp which I have just added a comment field .

Access is given an error no value for one or more required parameters

Here is the old code

Private Sub cmdDisplay_Click()
On Error GoTo Err_cmdDisplay_Click
Dim sql As String
DoCmd.SetWarnings off
sql = "DELETE * from tblTemp"
DoCmd.RunSQL sql


If Len(Me.txtQcp) = 0 Then
Exit Sub
End If

If Len(Me.txtUser) = 0 Then
Exit Sub
End If

Dim rs As ADODB.Recordset, i As Integer, rs2 As ADODB.Recordset, sql2 As String
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

sql2 = "tblTemp"
sql = "SELECT IIf(IsNull([UserID]),False,True) AS Accepted, tblQcpList.qcpID, tblQcpList.QcpList " & _
"FROM tblQcp INNER JOIN (tblQcpList LEFT JOIN qryQCPLinkUser ON tblQcpList.ID = " & _
"qryQCPLinkUser.ID) ON tblQcp.qcpID = tblQcpList.qcpID WHERE tblQcp.qcpID = " & Me.txtQcp & _
" GROUP BY IIf(IsNull([UserID]),False,True), tblQcpList.qcpID, tblQcpList.QcpList, tblQcpList.qcpID"

rs.Open sql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
i = 1
While rs.EOF = False
If IsNull(rs.Fields("QcpList")) = False Then
tmpList = Replace(rs.Fields("QcpList"), "'", " ")
Else
tmpList = ""
End If
sql2 = "insert into tblTemp (qcpID, Accepted, ListId, List) Values (" & rs.Fields("qcpID") & _
", " & rs.Fields("Accepted") & ", " & i & ", '" & tmpList & "');"
DoCmd.RunSQL sql2
rs.MoveNext
i = i + 1
Wend

rs.Close
rs2.Close
'DoCmd.RunSQL sql
Me![qryQcpList subform].Form.Requery
DoCmd.SetWarnings True

Exit_cmdDisplay_Click:
Exit Sub

Err_cmdDisplay_Click:
MsgBox Err.Description
Resume Exit_cmdDisplay_Click

End Sub


--------------------------------------------------------------------------


Here is the new code

Private Sub cmdDisplay_Click()
On Error GoTo Err_cmdDisplay_Click
Dim sql As String
DoCmd.SetWarnings off
sql = "DELETE * from tblTemp"
DoCmd.RunSQL sql


If Len(Me.txtQcp) = 0 Then
Exit Sub
End If

If Len(Me.txtUser) = 0 Then
Exit Sub
End If

Dim rs As ADODB.Recordset, i As Integer, rs2 As ADODB.Recordset, sql2 As String
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

sql2 = "tblTemp"
sql = "SELECT IIf(IsNull([UserID]),False,True) AS Accepted, tblQcpList.qcpID, tblqcplist.qcpcomment,tblQcpList.qcpList, " & _
"tblQcpList.ID FROM tblQcpList LEFT JOIN qryQCPLinkUser ON tblQcpList.ID = qryQCPLinkUser.ID " & _
"WHERE tblQcpList.qcpID = " & Me.txtQcp & " GROUP BY " & _
"tblQcpList.qcpID, tblqcpList.qcpcomment, tblQcpList.qcpList, IIf(IsNull([UserID]),False,True), tblQcpList.ID;"

rs.Open sql, CurrentProject.Connection, adOpenStatic, adLockReadOnly
rs2.Open sql2, CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
i = 1
While rs.EOF = False
If IsNull(rs.Fields("QcpList")) = False Then
tmpList = Replace(rs.Fields("QcpList"), "'", " ")
Else
tmpList = ""
End If
If IsNull(rs.Fields("Qcpcomment")) = False Then
tmpcomment = Replace(rs.Fields("Qcpcomment"), "'", " ")
Else
tmpcomment = ""

End If

sql2 = "insert into tblTemp (qcpID, Accepted, ListId, List,comment, ID) Values (" & rs.Fields("qcpID") & _
", " & rs.Fields("Accepted") & ", " & i & ", '" & tmpList & "'," & i & ", '" & tmpcomment & "' ," & i & ", " & rs.Fields("ID") & ");"
DoCmd.RunSQL sql2
rs.MoveNext
i = i + 1
Wend

rs.Close
rs2.Close
'DoCmd.RunSQL sql
Me![qryQcpList subform].Form.Requery
DoCmd.SetWarnings True

Exit_cmdDisplay_Click:
Exit Sub

Err_cmdDisplay_Click:
MsgBox Err.Description
Resume Exit_cmdDisplay_Click

End Sub


Can anyone tell me, where I am going wrong

 

[namliam]

Well for one thing you are not using the {code} and {/code} tags to post your code...
Replace {} with [] tho...

What query is it bombing out on?

One thing that seem wrong:

sql2 = "insert into tblTemp (qcpID, Accepted, ListId, List,comment, ID) " & _
"Values (" & rs.Fields("qcpID") & ", " & rs.Fields("Accepted") & ", " & i & ", '" & tmpList & "'," & i & ", '" & tmpcomment & "' ," & i & ", " & rs.Fields("ID") & ");"

You have only 6 fields in the tblTemp but you are filling it with 8 values... Of these 8 values you are using "i" 3 times, which seems wrong...

Instead of using Docmd.RunSQL and docmd.Setwarnings you can use Currentdb.Execute to run the SQL without prompt. Easier and more precise.