VB6 syntax problem, "no current record" error

Question

I am writing an app in vb6 using sql server 2005. here is my current code.

Dim Sqlstring As String
Dim rstCurrentTicket As Recordset

Sqlstring = "Select SubmiterName, LastViewDate, Department, Description, Urgency, SubmitDate, ResolvedDate from TroubleTickets where Title ='" + Trim(TicketComboBox.Text) + "'"
Set rstCurrentTicket = cnnSel.OpenRecordset(Sqlstring)


NameText.Text = rstCurrentTicket!SubmiterName
DeptText.Text = rstCurrentTicket!Department
Me.DescriptionText = rstCurrentTicket!Description
Me.UrgencyText = rstCurrentTicket!Urgency

when I run this code i recieve an error code saying:

"Run-Time error: '3021'" "no current record"

and it highlights this line of code:

NameText.Text = rstCurrentTicket!SubmiterName

any suggestions of how to fix this?

Answer 1

Your recordset has no results. You can check for this as follows:

If Not rstCurrentTicket.EOF Then
    NameText.Text = rstCurrentTicket!SubmiterName
    DeptText.Text = rstCurrentTicket!Department
    Me.DescriptionText = rstCurrentTicket!Description
    Me.UrgencyText = rstCurrentTicket!Urgency
End If

EOF = End Of File = the end of the recordset has been reached.

Answer 2

Keith is exactly right, but I wanted to give a little more detail

For ADO and DAO, you have a Begin-of-File marker (BOF)and an End-of-File marker(EOF). The records are returned like this

[BOF]
[Record one] <-
[Record two]
...
[Record n]
[EOF]

The arrow points to where the cursor is. The cursor points to which record in the record set that is returned.

When no records are returned, you get this

[BOF]
[EOF]

So, if both flags are set, there are no records. If EOF is set, either you have no records, or you've moved past the last record. (You move that cursor to the next record by this command.)

rstCurrentTicket.MoveNext

You can also check by

If (rstCurrentTicket.EOF and rstCurrentTicket.BOF) Then
     msgbox "There were no Trouble Tickets found."

Else
    'Do something here.

End If