bizarre conundrum - bookmark problem 3

[netscamp]

I have a subform and a main form.
The sub is a datasheet view of the main.

I have coded so that when I click the record selector(not the navigation button) in the subform, the main form sets itself to the same record number.

There are 842 records. When I click the subform it works fine, until I get to a record number over 450, then I get an error saying it is an invalid bookmark - runtime error 3159. I checked the underlying table and records > 450 are not noticably different than the lower numbered recs. All were imported from Excel at the same time, so, I doubt there are typos.

It seems like sometimes, I can got to record 479, someimes only 450.

here's the code...

Private Sub Form_Click()
' detect clicking into subform "MAIN3" and update main to sub

Dim transet As DAO.Recordset
Dim rex As Long
Dim curry As Long
curry = 0
Dim m3set As DAO.Recordset

Set m3set = Me.Form.RecordsetClone
m3set.MoveLast
m3set.MoveFirst

rex = m3set.RecordCount
'MsgBox (rex)

curry = Me.Form.CurrentRecord
'MsgBox (curry)
m3set.AbsolutePosition = curry - 1

Set transet = Me.Parent.RecordsetClone

'Parent.SetFocus

Me.Parent.Bookmark = m3set.Bookmark



End Sub

based on experience, it almost feels like there is some conflict with a variable/recordset already being open, or something overloading, overflowing, etc.

I have another button that jumps 10 records at a time, and using that code, I can go over the problem records mentioned above, without any errors.

P.S. if you have tons of time, and this strikes you as a wrong-headed, unecessarily complex approach, I wouldn't mind hearing about that, too.

Thanks,
Robert

 

[grtammi]

Robert:

I got this working for me:

1) Create a public variable (call it whatever you want, I'm going to use varBookmark here) as a Variant:

2) In the Form_Click event, place this code:

    ' declare and retrieve a clone recordset
    Dim rs As DAO.Recordset: Set rs = Me.RecordsetClone
    ' populate recordset
    rs.MoveLast: rs.MoveFirst
    ' get current record on this form
    Dim currPos As Long: currPos = Me.Recordset.AbsolutePosition
    ' move the clone recordset to the current recordset
    rs.Move currPos
    ' bookmark it!
    varBM = rs.Bookmark
    ' copy bookmark to parent form
    me.parent.recordset.bookmark = varBM

This worked for me on the form I designed quickly with 20 records ... should work for more though - let me know.

HTH


Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[Eupher]

netscamp,
I don't know if it relates to your problem at all, but you set the transet variable, but then you don't refer to it again.

Ken S.

 

[Eupher]

Okay, I tried Greg's approach on a recordset with just over 3000 records, and after about record 450 I started to run into problems. So I tried a different approach, using FindFirst instead, and it worked fine:

Dim Rs As DAO.Recordset
Set Rs = Me.Parent.RecordsetClone
If Not IsNull(Me![JobDetailID]) Then
    Rs.FindFirst "JobDetailID = " & Me![JobDetailID]
    Me.Parent.Bookmark = Rs.Bookmark
End If

In my table, "JobDetailID" is an autonumber, and is the primary key. Since your subform is just a datasheet view of the same table that's the record source of your main form, and assuming you have a unique primary key field, this approach should work.

HTH...

Ken S.

 

[Eupher]

Oops, forgot to release the variable:

Rs.Close
Set Rs = Nothing

'is this line redundant?

Ken S.

 

[KenReay]

Hi

Maybe I just do not understand bookmarks, but I thought they were only valid for a given Recordset (or its clone), so even if you requery a recordset, any previously saved bookmarks are no longer valid

Here bookmarks are being used across two recordsets (ie the recordsource of the subform, and the recordsource of the main form. Now they may in practice be the same, but they are not clones and theer is no absolute reason why the must be the same.

Is this not the basic problem, or do I need to read my 'Teach Yourself Bookmarks' chapter again ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Eupher]

Ken Reay, that makes sense to me. Maybe by sheer luck I avoided that problem in my solution, since I'm only calling the parent's recordset and using the subform as the lookup criteria. Thanks for the tip.

Ken S.

...still learning

 

[netscamp]

Call me Mr. Duh,.....

Here's what is now working with no recordset involved at all...

Dim currec  As Long
currec = Me.Form.CurrentRecord
DoCmd.GoToRecord acDataForm, "trans_table", acGoTo, currec

and it works on all 482 records. So, I guess I didn't need to do recordsets at all, and whether or not that is true, the mysterious 450 record limit still remains a mysterious limit.
Thanks for all your help, it restores one's faith in humanity.

Robert Winters

 

[Eupher]

Robert,
Be careful with your last approach. Even though you're not declaring a recordset, you're still basing your goto on the cursor's position within a recordset instead of on a specific value. Guess what happens if the user re-sorts the subform?

Ken S.