Wend with While error

captainlove

captainlove
Local time
Today, 14:40
Joined
Apr 18, 2008
Messages
39
Does anyone know what is wrong with this code, it is giving me a wend without while error


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 = ""
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
 
You're missing an END IF within this part:

Code:
If IsNull(rs.Fields("QcpList")) = False Then
tmpList = Replace(rs.Fields("QcpList"), "'", " ")
Else
tmpList = ""
If IsNull(rs.Fields("Qcpcomment")) = False Then
tmpcomment = Replace(rs.Fields("Qcpcomment"), "'", " ")
Else
tmpcomment = ""

End If
 
Aren't you going to point it out to the poor soul, Bob???? :)
You're missing an END IF within this part:

Code:
If IsNull(rs.Fields("QcpList")) = False Then
tmpList = Replace(rs.Fields("QcpList"), "'", " ")
Else
tmpList = ""
[COLOR="Red"][size=4][B]END IF[/B][/size][/COLOR] [COLOR="Red"]<------------[/COLOR]
If IsNull(rs.Fields("Qcpcomment")) = False Then
tmpcomment = Replace(rs.Fields("Qcpcomment"), "'", " ")
Else
tmpcomment = ""

End If
 
Thank you boblarson and ajetrumpet. I am new to access and new here. That was really precise and helpful, now the error is saying no value given for one or more required parameters
 
I have no idea, but here are a couple of ideas...
Set rs = New ADODB.Recordset <---
Set rs2 = New ADODB.Recordset <--- Here are the declarations for the sets

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 <--- You've already declared the "rs" variable, haven't you?
Don't you have to issue a "set" statement with the SQL for the recordset?​
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 = "" <--- is this variable declared in this procedure somewhere???
 
Error Message on database

Hi bob and adam

What I am trying to do is get
frmqcplist should be displayed for that user/qcp combination with the comments and listitems I have added in frmfilter


Errors message steps

on frmfilter

I add select a qcp, from the combo box

1) select qcp 50000
2)add a list to the list text box i.e list4
3)add a comment to the comment text box i.e comment4
4) Click on the accept entered list button
5) The data appears in the subform

frmqcplist1 Steps
1) click switchboard

2) select first button select qcp/list items (form frmqcplist1 opens up)

3) select user/qcp combination i.e peco/50000 and click display related records

4) click on the button open items database(error message: No value given for one or more required parameters)
 

Attachments

Users who are viewing this thread

Back
Top Bottom