learning ADO; finding a record

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christopher Glaeser - 30 Mar 2005 20:36 GMT

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

Reply to this Message

Christopher Glaeser - 30 Mar 2005 21:04 GMT

BTW, I'm using Access 2003.

Reply to this Message

Dirk Goldgar - 30 Mar 2005 21:24 GMT

> The code below uses ADO to "sequentially" search tblTasks for a
> matching key and then extract the value of another field in that
[quoted text clipped - 19 lines]
>
> 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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Christopher Glaeser - 30 Mar 2005 22:01 GMT

> 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

Reply to this Message

Dirk Goldgar - 30 Mar 2005 22:42 GMT

>> 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)

Oops! Sorry about that.

> and it works great.

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

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Christopher Glaeser - 30 Mar 2005 22:49 GMT

> 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

Reply to this Message