Problem moving through Records

[soloprogrammer]

I recently converted a Microsoft Access97 application to Microsoft Access 2000 with a SQL Server backend.

There were Previous and Next buttons, but when I want to go to different records, I get a "Can't go to specified record" error.

In testing this worked BEFORE I moved the tables to SQL Server Backend. The first form has a search on it. When you find the record you want, you double click on it, which fires the following code:
DoCmd.OpenForm "DataInputForm", acNormal, ,,,, Me.ListBoxSearchResults
--Like I said before, this part worked, until I moved the data over to SQL Server backend.

When I converted it to SQL Server backend, I had to change the code because it was always going to the first record instead of the specific searched record. So I modified the code to:
DoCmd.OpenForm "DataInputForm", acNormal, , "primary = " & Me.ListBoxSearchResults.Column(0),,, Me.ListBoxSearchResults

Now I get the "Can't go to Specified Record" error.

Any Help would be great!

 

[scking]

It's relatively easy to move to a selected record using Access, SQL Server data, and ADO recordsets. ADO only allows you to 'Find' a single field criteria but allows you to 'Filter' multiple records for a recordset.

Form_Open
Dim MyRecordset As New ADODB.Recordset
Dim varBookmark As Variant
Dim strCriteria As String
Set MyRecordset = Me.RecordsetClone


'PUT THIS SOMEWHERE IN AN AFTER_UPDATE EVENT
'AND TRIGGER IT AFTER VERIFYING ALL DATA IS
'AVAILABLE

' Mark the starting point
varBookmark = Me.Bookmark

' Create the Criteria
strCriteria = "MyPrimaryKey=" & Me.txtPrimaryKey

' Create a recordset containing all the records
' which pass the filter criteria
MyRecordset.Filter strCriteria

' If you actually have found some then
' move to the bookmark of the selected item
If Not MyRecordset.EOF Then
Me.Bookmark = MyRecordset.Bookmark
End If


---------------------
scking@arinc.com
---------------------