Bookmark code not working in Access 2007

I wrote a database in Access 2003 that tracks employee accidents. There is a main form that contains the Emplyee Name and ID. This form contains a subform that holds all the accident information (hence the one to many: one employee can have one or more accidents).

I also have a read only form that shows all the accidents. I have a button on this form that allows the user to go directly to the exact same record on the (non) read only input form to make edits. The following code works perfectly in Access 2002/2003 but fails in Access 2007:

Set frmSub = frm.subfrmAccidentInvest.Form
 With frmSub
    .RecordsetClone.FindFirst "[AccidentInvestID]=" & Me!AccidentInvestID  
    If Not .RecordsetClone.NoMatch Then
        .Bookmark = .RecordsetClone.Bookmark
    End If
 End With

In debug when I mouse over the line {If Not .RecordsetClone.NoMatch Then} the tooltip reads:
.Bookmark = <No current record.>

Again, the code works perfect in Access 2002/2003 and I have Microsoft DAO 3.6 Object Library listed above Microsoft ActiveX Data Objects 2.8 Library in my references.

Any help greatly appreciated.
Who is Participating?
A2007 introduced a VERY unhappy thing


Where Access 2003 may have saved a filter in a form's filter property, it didn't APPLY it when you opened the form.
A2007+ does. FilterOnLoad = Yes is the default.
That can be a very unhappy thing in a mixed 2003/2007+ environment

Since you are using recordsetclone, you would get any filtering that was in place.
As an experiment, open the form in design view.
Ensure that there is not filter string whatsoever.
Does it then behave like Access 2003 did?
Dale FyeCommented:
I usually do it this way:

Set frmSub = frm.subfrmAccidentInvest.Form
With frmSub.recordsetclone
    .FindFirst "[AccidentInvestID]=" & Me!AccidentInvestID  
    If NOT .NoMatch Then frmSub.Bookmark = .Bookmark
 End With
dhausnerAuthor Commented:
This code works the same as what I wrote (with the same error). The main form does open to the correct employee but instead of the record of the accident showing in the subform, it just sets up a new (blank) record (I forget that fact in my first post).
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Make sure that your subform's Data Entry property is set to "No"
dhausnerAuthor Commented:
Yup, it is.
dhausnerAuthor Commented:

Remember that this code does work perfectly in Access 2002/2003.
It must be either a syntax issue or the way Access 2007 is handling it.
Can you post a sample DB?
(I can't think of a reason off-hand for this code to be problematic in Access 2007.  I have very similar code running in my own Access 2007 databases without error)
What about trying something like this:
Dim rstAccident As DAO.Recordset

' clear any bookmark, by requery

                 Set rstAccident = Me.frm.subfrmAccidentInvest.Form.RecordsetClone
                 With rstAccident
                                 .FindFirst "[AccidentInvestID]=" & Me!AccidentInvestID 
                                 frm.subfrmAccidentInvest.Form.Bookmark = .Bookmark
                 End With
 ' clean up
 Set rstAccident = Nothing

Open in new window

Hope this helps,
dhausnerAuthor Commented:
Thank You so much Nick! That was it. I added FilterOn = False to the On Load event of the subform and now everything works perfectly.

Much appreciation also to eveyone who responded.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.