Main Form Requery and Set Bookmark Generates error 2001

Posted on 2006-05-31
Last Modified: 2007-12-19
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()
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
Question by:guilloryt

    Author Comment

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

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

    Expert Comment

    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.


    Author Comment

    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.
    LVL 58

    Accepted Solution

    Hello guilloryt

    I understand now. You are using an ADO recordset (I should have seen that), and under certain circumstances, Jet will use a double value as bookmark. Also, this value is sometimes identical with the absolute position of the record, so that you used it as a substitute for the .AbsolutePosition property.

    Your code does in fact this:

    If rs.RecordCount > rs.AbsolutePosition Then
      Me.Parent.Recordset.AbsolutePosition = rs.AbsolutePosition + 1
    End If
    Me.Parent.Recordset.AbsolutePosition = rs.AbsolutePosition

    A Bookmark, however, is used for mainly one thing: storing a position in a recordset and returning to that position. Using ADO, this is no longer a string, but a Variant. However (from ADO help):

    The user may not be able to view the value of the bookmark. Also, users should not expect bookmarks to be directly comparable—two bookmarks that refer to the same record may have different values.

    ADO introduces some new methods using bookmarks, like the CompareBookmark method. But bookmarks are still something different from record positions.

    Going back to your code, you can solve the scrolling issue in a different way:

    Me.Parent.Bookmark = rs.Bookmark
    DoCmd.GoToRecord acDataForm, Me.Parent.Form.Name, acNext
    DoCmd.GoToRecord acDataForm, Me.Parent.Form.Name, acPrevious

    This assumes that there is a "blank" record to go to. If not, you would have to test that as before.

    Hope this helps!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now