• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

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.
1 Solution
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).
Make sure that your subform's Data Entry property is set to "No"
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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,
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?
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.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now