How to goto a Record on a Subform - Object is not Open!!! 2

[dftjsn]

I have a main form named: frmCitationPicker that contains
a subform named: sfrmCitationDatasheet. There is a command button on the main form named: cmdAddNewCite. When this command button is clicked, it pops up another form named: sfrmCitations that allows the user to enter a new citation.

When the user closes sfrmCitations, they are back in the code below. Me!sfrmCitationDatasheet.Form.Requery requeries the subform sfrmCitationDatasheet whose recordsource is a query so that it will contain whatever new records the user entered in sfrmCitations.

This all works fine. The problem arises when I try to make the currently selected record be the citation they last selected. I have code that figures out the record number to go to but:

DoCmd.GoToRecord acDataQuery, "Forms!frmCitationPicker!sfrmCitationDatasheet.Form", acGoTo, ThisRecNum

gives an error that "The Object Forms!frmCitationPicker!sfrmCitationDatasheet.Form is not open."

Can anyone explain why I can't go to ThisRecNum and why the error arises?

Thanks!

dftjsn

Private Sub cmdAddNewCite_Click()
On Error GoTo Err_cmdAddNewCite_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "sfrmCitations"
DoCmd.OpenForm stDocName, , , stLinkCriteria, ,
acDialog, "GoToNewRec"
Me!sfrmCitationDatasheet.Form.Requery

 

[BillPower]

Hi dftjsn

DoCmd.GoToRecord acDataQuery, "Forms!frmCitationPicker!sfrmCitationDatasheet.Form", acGoTo, ThisRecNum

Remove .Form from the above. .Form is only used on a subform to reference a control.

If this doesn't work, you might need to set the focus to the control in the sub form that ThisRecNum applies to.

To do this:

Forms!frmCitationPicker!sfrmCitationDatasheet.Form!ControlName.enabled = true 'use if the control's disabled
Forms!frmCitationPicker!sfrmCitationDatasheet.Form!ControlName.visible = true 'use if the control's hidden
Forms!frmCitationPicker!sfrmCitationDatasheet.Form!ControlName.setfocus
Forms!frmCitationPicker!sfrmCitationDatasheet.Form!ControlName.enabled = false
Forms!frmCitationPicker!sfrmCitationDatasheet.Form!ControlName.visible = false
DoCmd.GoToRecord acDataQuery, "Forms!frmCitationPicker!sfrmCitationDatasheet", acGoTo, ThisRecNum

If this still doesn't work, let me know, there are lots of other ways to do what you are trying to do.

Regards

Bill

 

[dftjsn]

Bill,

Thanks for your pointers. They helped. What finally worked was simply setting the focus to the subform with the query results (sfrmCitationDatasheet) and only specifying acGoTo and ThisRecNum in the GoToRecord command as below:

sfrmCitationDatasheet.SetFocus
DoCmd.GoToRecord , , acGoTo, ThisRecNum

I still don't understand why full specification of the GoToRecord command wouldn't work:

DoCmd.GoToRecord acDataQuery, "Forms!frmCitationPicker!sfrmCitationDatasheet", acGoTo, ThisRecNum

Dropping the .Form didn't help. Dropping acDataQuery didn't help. I still got the error that "The Object Forms!frmCitationPicker!sfrmCitationDatasheet is not open."

Thanks again Bill!

dftjsn

 

[BillPower]

Glad to hear you got it working. Thanks for letting me know how, it all helps with my own and other's future problems. Deserves a star for being helpful.

Regards

Bill