Unnecessary code in ADODB methods?

[cityblm.is]

I use code similar to the code below in each of a few Access 2003
forms I've written. This is code that I've found on the Internet and
in books so I'm not able to cite each source, but it just seems like a
lot of code to me. Can any of this working code be eliminated? I like
the ADO method over the DAO, so I'm not looking for a totally new
method, I'm just wondering about this one...

'id is an integer variable declared at the top to be used throughout
the form

Private Sub lbxEmployees_DblClick()
On Error GoTo ERROR
Dim rst As ADODB.Recordset
Dim found As Boolean
Dim rsf As ADODB.Field
Dim temp As String

temp = ""
found = False
id = lbxEmployees.ItemData(lbxEmployees.ListIndex)
'Call resetFields

Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM qryEmployees WHERE autoID=" & id & ";",
CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
' This just seems like a lot of code...seems like too much
nesting.
With rst
While Not .EOF
For Each rsf In .Fields
If rsf.Name = "autoCM_ID" Then
If rsf.Value = id Then
temp = .Fields("txtFirstName") & " "
& .Fields("txtLastName") & " x" & .Fields("txtPhoneNumber")
Me.txtResults = temp
found = True
End If
End If
Next
.MoveNext
Wend
End With

If Not found Then
MsgBox "There was an error processing the record." & vbCrLf &
vbCrLf & "Please try again in a few moments or adjust the code.",
vbOKOnly, "ERROR"
Call resetFields
End If
rst.Close
Set rst = Nothing
Exit Sub
ERROR:
MsgBox "There was an error in the application.", vbCritical,
"ERROR(S)!"
Exit Sub
End Sub
' END CODE

Thank you for any input!

 

[cityblm.is]

I do want to mention, that the code should say autoID not autoCM_ID. I
am aware of that typo. It is correct in my program.

 

[Tom Y]

One thing I noted is that you are looping through all the fields to find
your field. If you know your field name (which it looks like you do in the:
If rsf.Name = "autoCM_ID"
line, then simply access the field as:
rsf!autoCM_ID
Thus you could have the code read:

If rsf!autoCM_ID = id then temp = rsf!txtFirstName & " " & ....

There is no need to loop through all fields.
Hope this helps.