asked on

Main Form Requery and Set Bookmark Generates error 2001

When I update a field in a subform I want to recalculate the associated values in the main form while maintaining the bookmark at the current row. The subroutine below generates an error 2001. I do not see what is wrong with the code.

Public Sub Requery_SalesHeader()

Me.Parent.Requery
Dim rs As ADODB.Recordset
Set rs = Me.Parent.RecordsetClone
rs.Find "[Sales docu]=" & Me.[Sales docu] ' find the row in the main form that matches the current subform row.

If rs.RecordCount > rs.Bookmark Then
Me.Parent.Bookmark = rs.Bookmark + 1 ' error generated here
End If
Me.Parent.Bookmark = rs.Bookmark

End Sub

Richard

ASKER

The error appears to occur only when I am set a filter on the main form.

Forms![frmSalesHeader].FilTer = strSql
Forms![frmSalesHeader].FilterOn = True

Markus Fischer

Hello guilloryt

There are several problems with your code sample:
• you use .Find on rs, but never check or use the result
• you compare the total number of records with a bookmark
• you attempt to calculate with a bookmark

A bookmark is not a number. It's a string expression used to identify a record within a given recordset, but it has nothing to do with a record number.

I believe you will have to invent another logic for what you need to do.

Cheers!
(°v°)

Richard

ASKER

Thats odd. It works as long as I do not have an active formfilter. It allows the user to update the data in a subform field. In the after update event on that subform field the requery sales header forces the parent form to requery and then point the parent forms bookmart to the primary key that is selected in the subform. If it is not the last record then the main form moves to +1 past the desired row and back one. The reason for this is it allows both the parent form and subform to be open in datasheet and allow viewing of the all the data. Otherwise some of the subform datasheet rows may scroll off the screen.

ASKER CERTIFIED SOLUTION

Markus Fischer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial