Search code examples
vbams-accesssubformcontinuous-forms

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

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!

Solution

  • 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