Moving To A Record in Access Project

[Joe Delphi]

Hi,

I have a MS Access project that uses MS-SQL Server for the backend
database engine. In the project, I have a form with a listbox on it that I
want the user to use for selecting a record for editing. I am trying to
figure out how to move the data in the form to the user-selected record. I
tried this:


Me.RecordsetClone.Findfirst "[ID] = " & Me![lstbxPlans]
Me.Bookmark = Me.RecordSetClone.Bookmark


but I received an error message that said something like RecordSetClone can
only be used with the Jet Database Engine (an .mdb file) and not in other
situations. My ListBox is set up so that the bound column is the primary
key of the underlying table. I want to use the OnDoubleClick event of the
listbox. So how do I move the form to the user-selected record in an .adp
file?

Any help appreciated,
JD

 

[Sylvain Lafontaine]

Use something like:

If (Me.Recordset.BOF And Me.Recordset.EOF) Then Exit Sub

With Me.RecordsetClone
.Find "[IdLigue]=" & Me!ComboIdLigue

If (.BOF Or .EOF) Then
.movefist
If (.BOF Or .EOF) Then Exit Sub
End If

Me.bookmark = .bookmark
End With

Note 1: with ADP, you have ADO recordsets, not DAO recordsets and ADO
recordsets don't have the .FindFirst method. Also, the .Find() function
have other parameters:

Find(Criteria As String,
[SkipRecords As ADO_LONGPTR],
[SearchDirection As SearchDirectionEnum = adSearchForward],
[Start])


Note 2: I don't remember if the RecordsetClone method is a singleton or not.
If it's not, then writing Me.RecordSetClone.Bookmark a second time create a
second recordset, distinct from the first. To avoid this, use the With
command as above or set the recordset to a variable:

Dim rs as ADO.Recordset
set rs = Me.RecordsetClone
rs.bookmark = Me.bookmark
....