Can't move subform to next record (1 Viewer)

LindaLooUK

Registered User.
Local time
Today, 00:10
Joined
Dec 30, 2014
Messages
10
Hi everyone

I really hope you can help as I've spent way too long on trying to solve this issue myself. :eek: :banghead: I'll try and be brief.

FormA is making a call to FormC that is a subform of FormB. So, FormA is asking FormC to move to the next record and then provide the details of this next record to FormA. This works fine in as much as the correct details are displayed in FormA but FormC doesn't actually move to the next record!

In FormA I'm calling the (public) sub that's behind the NextRecord nav button on FormC (DoCmd.gotorecord,,acNext). If I then attempt to go to the previous record (from FormA) it fails because FormC still thinks it's on the first record and I obviously can't then go to a previous record.

This is the code in FormA:

Forms!frmB.frmC.SetFocus
Call Forms("frmB").Controls("frmC").Form.cmdNextCand_Click

How can I force FormC to actually move to (i.e. display) the next record when called from FormA?

I hope this makes sense! ;)
 
Last edited:

sneuberg

AWF VIP
Local time
Yesterday, 17:10
Joined
Oct 17, 2014
Messages
3,506
I suggest doing this by manipulating the bookmark in the target form. Below is some code I've used to move the parent of a subform to the next record from the subform. Basically follow the pattern in the code below except replace Me.Parent in this code to the the full reference of the target form. This web page is useful for figuring out references.

Code:
Dim rs As DAO.Recordset
Set rs =[COLOR="blue"] Me.Parent[/COLOR].RecordsetClone
rs.MoveNext
[COLOR="blue"]Me.Parent[/COLOR].Bookmark = rs.Bookmark
Set rs = Nothing
 

LindaLooUK

Registered User.
Local time
Today, 00:10
Joined
Dec 30, 2014
Messages
10
Many thanks for the reply but unfortunately this doesn't work either as it's still displaying the first record on FormC.

There is a further subform on FormC (FormD) but that shouldn't affect the movement of records on the first subform should it.

FormA

FormB
FormC (subform)
FormD (subform in subform)

If I physically click on the nav buttons on FormC it moves the records around as expected but not if calling from FormA.

Weird huh?!
 

sneuberg

AWF VIP
Local time
Yesterday, 17:10
Joined
Oct 17, 2014
Messages
3,506
If upload you database I'll see if I can figure out why this is not working for you. If the reference are correct I don't understand why it wouldn't.
 

sneuberg

AWF VIP
Local time
Yesterday, 17:10
Joined
Oct 17, 2014
Messages
3,506
I tested the code I was suggesting in the attached database and it did have a flaw in that it wasn't updating the recordset clone with the current record of the subform. I fixed that in the following code which is demonstrated in the attached database.

Code:
Dim rs As DAO.Recordset
Set rs = [Forms]![FormB]![FormC].[Form].RecordsetClone
rs.BookMark = [Forms]![FormB]![FormC].[Form].BookMark
rs.MoveNext
[Forms]![FormB]![FormC].[Form].BookMark = rs.BookMark
rs.Close
Set rs = Nothing
 

Attachments

  • MoveToNextRecInSubForm.accdb
    492 KB · Views: 95

LindaLooUK

Registered User.
Local time
Today, 00:10
Joined
Dec 30, 2014
Messages
10
Bless you for posting an example database - it works a treat!!

I really do appreciate all your efforts with this, thank you. :)
 

ADBirch

New member
Local time
Yesterday, 20:10
Joined
Sep 9, 2019
Messages
3
I tested the code I was suggesting in the attached database and it did have a flaw in that it wasn't updating the recordset clone with the current record of the subform. I fixed that in the following code which is demonstrated in the attached database.

Code:
Dim rs As DAO.Recordset
Set rs = [Forms]![FormB]![FormC].[Form].RecordsetClone
rs.BookMark = [Forms]![FormB]![FormC].[Form].BookMark
rs.MoveNext
[Forms]![FormB]![FormC].[Form].BookMark = rs.BookMark
rs.Close
Set rs = Nothing

I found this post and this code worked perfectly for me! Next Question:

In my database, I am in FormA, click on the button I created, and the focus moves to either the next or previous record on FormC, which is a Subform of FormB and is a Datasheet.

What I now want to do is load the data from that record on FormC to view on FormA.

I tried a number of commands including Requery(ing) the form, Closing the form and trying to reopen it from the record that has the focus with the command:
"DoCmd.OpenForm "FrmIncidentEdit", "[IncidentID]=" & [IncidentID], , acNormal" which I use to open the form initially.

I get the error message "The expression you entered refers to an object that is closed or doesn't exist."

I tried removing the close command, with no avail.

Suggestions?

Thanks, Alan
 

ADBirch

New member
Local time
Yesterday, 20:10
Joined
Sep 9, 2019
Messages
3
Dim rs As DAO.Recordset
Set rs = [Forms]![FormB]![FormC].[Form].RecordsetClone
rs.BookMark = [Forms]![FormB]![FormC].[Form].BookMark
rs.MoveNext
[Forms]![FormB]![FormC].[Form].BookMark = rs.BookMark
rs.Close
Set rs = Nothing

I used the above code and it works perfectly!

I have a "Next Question":

When in FormA, I have buttons that move to the Next and Previous records on FormC which is a subform of FormB. I now want to load the data from the selected record on FormC into FormA.

I tried ReQuery. Closing FormA and reopening from FormC. Nothing seems to work.

Anyone have a solution?

Thanks,
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:10
Joined
May 7, 2009
Messages
19,169
does your formA has same field as in subform C?
what do you mean by Load, add a record?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:10
Joined
Aug 30, 2003
Messages
36,118
Post 7 was moderated, I'm posting to trigger email notifications.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:10
Joined
Feb 19, 2002
Messages
42,981
This whole thread mystifies me. Forms are the way humans interact with data. Code should be using action queries or recordsets rather than automating forms.
 

ADBirch

New member
Local time
Yesterday, 20:10
Joined
Sep 9, 2019
Messages
3
Thanks for the replies.

It took me some time to work out the solution:

Once the pointer has moved...

1) Close FormA
2) SetFocus to the record number on FormC that the pointer moved to... Syntax: Forms!FormB!FormC.Form.Record Number
3) Store the record number in a new variable (Same syntax as above)
4) Open FormA with the new record number.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:10
Joined
Feb 19, 2002
Messages
42,981
Still mystified. Recordset processing should not be done by automating forms.
 

Users who are viewing this thread

Top Bottom