Filtered Subform as Recordsource

[xentaur]

Greetings wise ones

FrmMain comprises 2 subforms.
Sub1 has scrolling list of all client names.
Sub2 has specific details of client's records.

Both are subforms of FrmMain, not one of the other.

The user can:

a) clicks on a name in sub1 which filters sub2 to show only details of that client's particular record; or

b) can apply filters to all records via sub2 to search for, for the sake of the example, all clients with the same postcode + last name begins with a 'q'.

Standard stuff.

All above works fine, Until it comes to generating a report based upon the filtered recordset. The following code is in the on_open event of my report:

Private Sub Report_Open(Cancel As Integer)
Dim frm As Form
Set frm = Forms!FrmMain.Sub2.Form
Me.RecordSource = frm.RecordSource
If (frm.FilterOn) Then
Me.Filter = frm.Filter
Me.FilterOn = True
End If
   Me.OrderBy = frm.OrderBy
   Me.OrderByOn = True
End Sub

Now,

- Report will generate fine using (a) above.
- Can generate a report of all records if they are not filtered; but
- try to filter records via sub2 and open report, the report asks for the parameter value for the control by sub2 was filtered.

Have tried 'Forms!FrmMain.Sub2.Form' vs 'Forms!FrmMain!Sub2.Form' with no success.

What makes this all the more infuriating is that this code works just fine when data is not included in a subform but on the mainform. ie Forms!FrmMain.Form

Any ponderances gratefully acknowledged.

Regards

xentaur

 

[xentaur]

Anybody??

 

[terre]

Greetings Xentaur

Not a solution I'm afraid!
I have a main form, amd subform........and would dearly like to know how to filter the subform form a combobox selection on the main form!!

Can you help please

 

[MajP]

Can you print out the filter string. I assume in that in the string there is a reference to a value in the control that does the filtering. Something like
"id = " & me.txtBx.value
If that is the case Me in your report is looking for that control on your report, but it resides on your form. Lets see the filter.

 

[MajP]

terre,

Private sub cmbSelect_change()
dim strFilter as string
strFilter = "numfieldName = " & cmbSelect.value
'or
'strFIlter = "strfieldName = '" & cmbSelect.value & "'"
with me.controls("subControlName").form
.filter = strFilter
.filteron = true
end with
end sub