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

Time:08-24

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
  • Related