Navigation code for two identical subforms in different views

[jondavidf]

Hey all,

I've created a main form (frmMain) with a subform (frmSub) and sub-subform (frmSubSub). Both of the subforms are identical, with frmSub in form view and frmSubSub in datasheet view. I'm basically trying to make a sub-split form. The forms are for viewing data only, no data entry is required on either form. Please see screenshot below.



What I'm trying to do is just navigate both forms with one set of command buttons. I tried setting up the forms by linking Master/Child values but then frmSubSub returns only the same record as frmSub. I also tried adding the following code to my cmdNext button which is located in frmSub:

Me.frmSubSub.Form.Recordset.Movenext

No solution I have found has seemed to work.

I thought this would be pretty straight forward but it's been proving to be quite a challenge (for me!).

What do you think the best options are?

Thanks,

Jon

[Perceptus]

Completely not tested, but in theory.

' Code for the First subform On Current Event
Sub Form_Current()
Me.frmsubsub.form.recordset.bookmark = me.frmsub.form.recordsetclone.bookmark
end sub

[isladogs]

Have a look at the emulated split form at https://www.access-programmers.co.uk...d.php?t=294421
You should be able to adapt it for your purposes.

And FYI it was a challenge that went through many iterations before it behaved exactly as required.
Don't even think of using the standard Access split form for this.

[jondavidf]

Thanks Ridders! I just got around to working on this and was able to modify it for my needs. It works amazing! I really can't thank you and the other folks that worked on this enough. Thank you! Thank you!

Jon

[jondavidf]

Hey all,

I know the last thing you probably want to do is look at this form again, but I'm having an issue with the Record Counting feature of the form. It woks fine when I run it as a stand alone form, but when I insert it as a subform I get Error 3021 and it asks me to Debug the numrecs section. I've found that if I blank out the function it works as a subform, just without having a total count of records. Any ideas?

The problem seems to be at (where I inserted the ' symbol):

Private Function numrecs() As Long    
    'RecordsetClone.MoveLast
    'numrecs = RecordsetClone.RecordCount
End Function

Which is called from:

Private Sub Form_Load()
    RCount = numrecs
End Sub

It seems like it's trying to count the records before they are loaded but I'm not sure what the right answer is to fix it.

Thanks again for all the help! I REALLY do appreciate it.

Jon

[isladogs]

It's difficult to tell from the snippet of code but for info the subform is loaded before the form has fully loaded which may explain your issue.
You could miss out your function and try just setting RCount equal to the record count
Or use DCount("*", "your subform record source name here")

[jondavidf]

That sounds like exactly what I need to do. How do I set RCount equal to the record count? And would I put that in the Form_Load procedure?

Just FYI, I put Rcount = numrecs on the cmdNext Click event and it does then display the correct number of records, but of course not until that button is pushed.

Thanks again!

[isladogs]

You could perhaps just use this and miss out the middle stage:

RCount = RecordsetClone.RecordCount

It may be better to do so in the Form_Current event especially if the record count could change whilst the form is open

However, I would probably use DCount instead - unless the count was very large (when it may be too slow)
For info .RecordCount won't work on linked tables but DCount will

This link gives a good summary of ways of counting records https://www.access-programmers.co.uk...d.php?t=224031

[jondavidf]

Got it! Thanks so much!

Jon

[isladogs]

You're welcome