Wrapping forms, filters, VBA and variable names

[PaulWilson]

Premise:
It might be useful to take a collection of forms that work well together and "wrap" them in an outer form that is nothing more than formatting and navigation.


I have tblFirstName and tblLastName
tblFirstName and tblLastName exist in a 1 to many relationship. tblFirstName is the one side and tblLastName is the many side.
tblLastName has a foreign key that links to the tblFirstName primary key.

qryFirstName and qryLastName have been created based on tblFirstName and tblLastName

frmNames has qryFirstName as recordsource.
fsubLastName has qryLastName as recordsource

frmNames shows first names in the main form with fsubLastName as a subform presented as a datasheet.

A dblclick event procedure is placed on the ID field in fsubLastName.

If you doubleclick the ID field in fsubLastName, a second form opens.
The second form is nothing more than a filtered version of fsubLastName called fsubLastName_filtered.
The ID field was passed to fsubLastName so only one record shows.
The "filtered" icon is lit up at the bottom of the fsubLastName_filtered when you get there via the double-click event.

The essential lines of code are as follows:

Option Compare Database
Option Explicit

Dim LastNameID As Long
Private Sub idsLastNameID_DblClick(Cancel As Integer)

On Error GoTo ErrorHandler
   
   LastNameID = Nz(Me![idsLastNameID])
   If LastNameID <> 0 Then
      Call FilterLastName(LastNameID)
   End If
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Private Sub FilterLastName(lngID As Long)

On Error GoTo ErrorHandler
      
   Dim strFilter As String
   
   strFilter = "[idsLastNameID] = " & lngID  'works if not in a subform
   DoCmd.OpenForm FormName:="frmLastName_filtered", view:=acNormal, wherecondition:=strFilter
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

ALL IS WELL!

But then trouble starts. I need to "wrap" these forms in a colorful template form (to be used with all database forms).
I create the wrapper form. It's not linked to a recordsource. It's only a wrapper with pretty colors and navigation buttons.

Let's say I make two copies of the wrapper form. frmPrettyFirstName and frmPrettyLastName_filtered

I drop frmFirstName into frmPrettyFirstName (as a subform).
I drop fsubLastName_filtered into frmPrettyLastName_filtered (again as a subform).

I change the code as follows:

DoCmd.OpenForm FormName:="frmPrettyLastName_filtered", view:=acNormal, wherecondition:=strFilter

The filtering no longer works.

I guess I need to adjust strFilter in the vba code above so that it reflects the new nesting of forms.
But how?

I tried using the expression builder to get the fully-qualified name for the field on the subform, but apparently VBA wants something more!
What is it ???
Is the Access forms design flexible enough to handle this?

 

[spikepl]

You are setting a filter for the parent form. Access cannot guess that you intend to filter the subform. You can pass data to the form being opened in OpenArgs, http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm and from that you could set the filter of the subform.

 

[PaulWilson]

Thanks Spikepl,
In your solution, the "wrapper" form would take on some new character. It would have an onload event procedure and also a means of storing the variable passed to it by the calling form. Is that right?

One guy suggested that I place a hidden field in the wrapper form and populate that field from the calling procedure. The problem with that is that each wrapper form must have a custom hidden field linked to the subform.

Still pondering....

 

[spikepl]

you can set the filter (and FilterOn) of any form or subform from anywhere if you use the full reference to the form or subform, and thus bypass your wrapper form altogether

 

[spikepl]

or the subform could permanently have a filter set to the parent form's OpenArgs, or to a string declared at class or module level, or there could be many other ways to skin the cat:

 

[PaulWilson]

aha!
The "full reference to the subform" is probably exactly the solution.
I even used the expression builder to reference the field in the subform.

The trouble is that I've tried various incantations of the vba syntax with the . and the ! and the [ and such and nothing seems to work as advertised. The form loads un-filtered.

 

[spikepl]

Then more incantations are in order

 

[spikepl]

Forms.YourParentForm.YourSUbfrom.Form.Filter=YourFilterString
Forms.YourParentForm.YourSUbform.Form.FilterON=True