learning ADO; finding a record

[Christopher Glaeser]

The code below uses ADO to "sequentially" search tblTasks for a matching key
and then extract the value of another field in that record to open a form.
Given that TaskID is the primary key of tblTasks, what is the efficient ADO
code to find that record? Any other commens on improving this code also
welcome.

Private Sub ctMDay0_AppDblClick(ByVal nIndex As Integer)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblTasks", CurrentProject.Connection

rst.MoveFirst
Do Until rst.EOF()
If rst!TaskID = Me.ctMDay0.AppointData(nIndex) Then
DoCmd.OpenForm "frmWorkOrder", , , "WorkOrderID=" & rst!WorkOrderID
Exit Do
End If
rst.MoveNext
Loop
rst.Close

End Sub

 

[Christopher Glaeser]

BTW, I'm using Access 2003.

 

[Dirk Goldgar]

The code below uses ADO to "sequentially" search tblTasks for a
matching key and then extract the value of another field in that
record to open a form. Given that TaskID is the primary key of
tblTasks, what is the efficient ADO code to find that record? Any
other commens on improving this code also welcome.

Private Sub ctMDay0_AppDblClick(ByVal nIndex As Integer)

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "tblTasks", CurrentProject.Connection

rst.MoveFirst
Do Until rst.EOF()
If rst!TaskID = Me.ctMDay0.AppointData(nIndex) Then
DoCmd.OpenForm "frmWorkOrder", , , "WorkOrderID=" &
rst!WorkOrderID Exit Do
End If
rst.MoveNext
Loop
rst.Close

End Sub

It will usually be faster to open the recordset on a query that returns
only the one record you want. I'm not an ADO expert, as I use DAO for
most things, but I expect that your code would look something like this:

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = _
"SELECT WorkOrderID FROM tblTasks " & _
"WHERE TaskID = " & Me.ctMDay0.AppointData(nIndex)

Set rst = New ADODB.Recordset
With rst
.Open strSQL, CurrentProject.Connection
If Not .EOF Then
DoCmd.OpenForm "frmWorkOrder", , , _
"WorkOrderID=" & !WorkOrderID
.Close
End With

 

[Christopher Glaeser]

It will usually be faster to open the recordset on a query that returns

only the one record you want. I'm not an ADO expert, as I use DAO for
most things, but I expect that your code would look something like this:

Thanks! Did a minor tweak (added an End If) and it works great.

Best,
Christopher

 

[Dirk Goldgar]

Thanks! Did a minor tweak (added an End If)

Oops! Sorry about that.

and it works great.

Very good. I'm glad my error was one that you could spot easily.

 

[Christopher Glaeser]

Very good. I'm glad my error was one that you could spot easily.

Yes, and it is exremely helpful to know how to build and use these queries
in code. Now I can go back and improve some forms that will benefit from
ADO coding. Thanks again.

Best,
Christopher