Please help with SQL query error if no data is present 2

[beetlebailey]

Please help if you can with the following SQL query. I am using a ADODB connection from Excel 2000 to a SQL 2000 database. If all three testers are present everything is fine and this query works well. But if one tester gets turned off only 2 testers are found by the query and the last tester (now absent from the database) produces the error: "Either EOF or BOF is true, or the current record has been deleted. Requested operation requires a current record." Is there a way to program for this condition? Thank you so much for any assistance.


SQLa = "Select top 1 Tester, Part from vpd where Line=" & Line
Set rs = cn.Execute(SQLa, , adCmdText)
T1 = rs.Fields("Tester").Value: .Range("A14").Value = T1
PT1 = rs.Fields("Part").Value: .Range("C14").Value = PT1
SQLb = "Select Tester, Part from vpd where Line=" & Line & " and Tester <>" & T1
Set rs = cn.Execute(SQLb, , adCmdText)
T2 = rs.Fields("Tester").Value: .Range("A15").Value = T2
PT2 = rs.Fields("Part").Value: .Range("C15").Value = PT2
SQLc = "Select Tester, Part from vpd where Line=" & Line & " and Tester <>" & T1 & " and Tester <>" & T2
Set rs = cn.Execute(SQLc, , adCmdText)
T3 = rs.Fields("Tester").Value: .Range("A16").Value = T3
PT3 = rs.Fields("Part").Value: .Range("C16").Value = PT3

 

[SkipVought]

Hi,

Before accessing the recordset values, if BOF & EOF then you have no rows.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[PHV]

Simply test the rs.BOF and rs.EOF properties:
Set rs = cn.Execute(SQLc, , adCmdText)
If Not (rs.BOF Or rs.EOF) Then
T3 = rs.Fields("Tester").Value: .Range("A16").Value = T3
PT3 = rs.Fields("Part").Value: .Range("C16").Value = PT3
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886