How to reference a subform value that isn't visible using Access VBA?

Question

I've got a subform in Access which represents the junction table in a many-to-many relationship.

Specifically, it's based on a query which includes values across all 3 tables (People, Trips, People_Has_Trips). The subform (subfrmTripsPeople) is part of the main form (Trips). It shows contact information, and includes a button labeled "Edit this person", the intended purpose of which is to open the People form to the selected record in the subform. This seemed like the right call over just having the fields be editable from the subform, to be sure the user clearly intends to edit the user.

Here's the VBA code I have so far:

Private Sub cmdEditPerson_Click()

Dim selectedPerson As Integer
selectedPerson = Me.subfrmTripsPeople.Form!People_PersonID

DoCmd.OpenForm "People", , , "ID = " & selectedPerson

End Sub

When I go into the form and click "Edit this person", I get "Compile Error: Method or Data Member not found" with subfrmTripsPeople highlighted in blue and the Private Sub line highlighted in yellow in the VBA editor. So it seems I've discovered an incorrect way to do this. What, then, have I done wrong and what is the correct way to do this?

The obvious issue, in my mind, would be that I'm referencing the field People_PersonID which isn't actually a field on the subform, though I assumed it would be accessible since it's part of the underlying source query. Yet, the highlighted code would seem to indicate that my error lies in the way i refer to the subform. I can't see what would be wrong with this, and I haven't misspelled the name or anything.

Would appreciate guidance on this issue!

Is it visible, but 0width, 0 height, or is it just not there? — Nathan_Sav
I deleted the control after generating the subform. The form source is still the query which includes it, though. — Lauren Berns III
Add it back with 0 width and height, and send to back just to be on the safe side :) — Nathan_Sav
Yes, it should still find field included in form RecordSource even if there is not control bound to field (reports behave different), however, doesn't hurt to have a hidden control (set as not visible). Does field name actually have People_ prefix? — June7

Lauren Berns III Lauren Berns III · Accepted Answer · 2019-12-20T15:12:11

I ended up adding a TextBox field (called invisiblepersonID) with a control source of people_personID, setting its width and height to 0.
The variable declaration turned out to be superfluous, so I got rid of it. Here's my new VBA code:

Private Sub cmdEditPerson_Click()

    DoCmd.OpenForm "People", , , "PersonID = " & Me!invisblepersonID

End Sub

How to reference a subform value that isn't visible using Access VBA?

1 Answers