Can't move subform to next record

[LindaLooUK]

Hi everyone

I really hope you can help as I've spent way too long on trying to solve this issue myself. :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!

 

[sneuberg]

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.

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]

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]

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]

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.

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

 

[LindaLooUK]

Bless you for posting an example database - it works a treat!!

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

 

[ADBirch]

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.

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]

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]

does your formA has same field as in subform C?
what do you mean by Load, add a record?

 

[pbaldy]

Post 7 was moderated, I'm posting to trigger email notifications.

 

[ADBirch]

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.