rs.Find - Runtime error "Rowset does not support scrolling backwards

[allyson787]

I am going through all the name fields in my table and adding them to a listbox. When a name is doubleclicked on the listbox I want my textboxes to show the other fields for that persons record from a table with the same EmployeeNumber (datatype as string).
There is a runtime error rowset does not support scrolling backwards with my find method. How do I get this to work? Here is my code.

Private Sub lstEmployee_DblClick()
SQL = "SELECT employee1.* from employee1;"
Set rsEmp = gCnn.Execute(SQL)
rsEmp.MoveFirst
strEmpLastName = lstEmployee.Text
rsEmp.Find ("[EmpNum] = '" & strRec & "'")
Do Until rsEmp.EOF
strEmpNum = rsEmp.Fields("EmpNum") & ("")
strFirstName = rsEmp.Fields("EmpFirstNme") & ("")
strAddress = rsEmp.Fields("EmpAddress") & ("")
strCity = rsEmp.Fields("EmpCity") & ("")
strState = rsEmp.Fields("EmpState") & ("")
txtFName2.Text = strFirstName
txtLName2.Text = strEmpLastName
txtCity.Text = strCityloop
txtState.Text = strState
txtAddress.Text = strAddress
txtZipCode.Text = strZipCode
Loop
rsEmp.Close
End Sub

 

[vb5prgrmr]

Welcome to TT allyson787. To get the most from this site please read FAQ222-2244.

You should use the open recordset method. I believe the execute method is read and forward only, hence the error. You will have to look that up in the help files.

Good Luck

 

[RJFost]

Assuming employee numbers are unique values, why not tweak the query to:

"SELECT * FROM employee1 WHERE EmpNum = '" & strRec & "'; "

Since there's only the one record for each employee number, that obviates the need for any sort of loop; you just load your controls with the field values & you're off to the races.

And, for the record, part of the problem with your code above is that your loop doesn't iterate- you're beating on the same record over & over. The MoveFirst puts you at the beginning of the recordset, then the Find (probably should use FindNext; it only moves forward through the records) puts you on the employee record. Why the loop in the first place? Provided you put a rsEmp.MoveNext before the Loop statement, all you'd be doing is loading the data for the employee you wanted, then immediately blowing that away with the data for the next employee, which is then blown away by the next, etc. When the dust settled, your form would be displaying the data for the last employee in the recordset. Once the FindNext puts you on target, load the field values & get out.