MS Access VBA code in continuous form not working while a subform-CodePudding

Good afternoon,

I'm running into an issue with some code. Basically I have a subform attached to a question and answer table.

The subform displays the question and the button displays the answer.

This works perfectly when I open the form directly, but it won't work as a sub.

Here is the original code:

Set r = Forms![FAQs_Questions].RecordsetClone 'Clone the recordset
r.Bookmark = Forms![FAQs_Questions].Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value

But when it's a subform, I get an error that says "...cannot find the referenced form 'FAQs_Questions'."

So I tried a bunch of things were I'd reference the main page first, below are all of my attempts, each has failed.

Dim r As DAO.Recordset
Set r = Forms![FAQs]![FAQs_Questions].RecordsetClone 'Clone the recordset
r.Bookmark = Forms![FAQs_Questions].Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value


Dim r As DAO.Recordset
Set r = FAQs.FAQs_Questions.Form.RecordsetClone 'Clone the recordset
r.Bookmark = FAQs.FAQs_Questions.Form.Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value


Dim r As DAO.Recordset
Set r = FAQs_Questions.Form.RecordsetClone 'Clone the recordset
r.Bookmark = FAQs_Questions.Form.Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value

Dim r As DAO.Recordset
Set r = Forms!FAQs_Questions.Form.RecordsetClone 'Clone the recordset
r.Bookmark = Forms!FAQs_Questions.Form.Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value


Dim r As DAO.Recordset
Set r = Forms!FAQs_Questions.Form.FAQs_Questions.RecordsetClone 'Clone the recordset
r.Bookmark = Forms!FAQs_Questions.Form.FAQs_Questions.Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value

I'm at a loss. Can anyone point me in the right direction?

Thank you!

CodePudding user response：

Since your VBA is code behind the subform button, you can simplify this thing by referencing RecordsetClone and Bookmark via Me (the current form; the one which contains the code).

Dim r As DAO.Recordset
Set r = Me.RecordsetClone 'Clone the recordset
r.Bookmark = Me.Bookmark 'Navigate to the active record
MyAnswer = r!Answer.Value

That approach should work regardless of whether the Me form is operating as a subform or if it was opened directly as a top-level form.

However, if you don't absolutely need to go the RecordsetClone and Bookmark route, just retrieve Answer.Value directly from the current row of the form's recordset:

MyAnswer = Me.Recordset!Answer.Value