Looking for Code to Expand Subform Rows Automatically

[kenk]

I've created a form that contains five levels of subforms - each a subform of
the parent above it. Each subform is displayed as a datasheet.
Form
Subform1
Subform2
Subform3
Subform4

Normally, the user selects a key from a combo box on Form that filters the
recordset of Form to a particular record. Access populates the Subforms
based on parent/child relationships between the tables used as data sources
for the Subforms. I then leave it up to the user to navigate through the
subform rows by manually expanding and/or contracting the various row
indicators.

Now I've found a case where I would like to have Access automatically expand
the necessary subform rows to display a particular row on Subform3. The user
will supply the key data to identify the row to be pointed to on Subform3 by
using another combo box on Form. A query will provide the key data necessary
to identify the applicable Form record and Subform1 and 2 rows.

So, with all the key data available, I’m looking for the code to cause
Access to expand the necessary rows and focus on the Subform3 row selected.

 

[JString]

VBA help provides this example:

Dim strExpand As String

With Forms("Purchase Orders")

strExpand = InputBox("Expand subdatasheets? Y/N")

Select Case strExpand
Case "Y"
.SubdatasheetExpanded = True
Case "N"
.SubdatasheetExpanded = False
Case Else
MsgBox "Can't determine subdatasheet expansion state."
End Select

End With

I'm *guessing* that you could have some level of control at least over which
subdatasheet is expanded by placing similar code in the subform that
immediately contains the subform you want to expand. You could also develop
some conditions to control when this occurs, but I'm guessing that once the
code executes, ALL of the visible rows in a particular subform will be
expanded.