Subform filtering workaround

[Paul_B]

ok, i've discovered filtering does not work on subforms so i have searched the forum and modified some code to this:

Dim strSQL As String

strSQL = "Select * from tasks where"
strSQL = strSQL & Me!frmSubProjects.Form!Project = 2
Me!frmSubProjects.Form.RecordSource = strSQL


error message is " can't find the field 'frmSubProjects' referred to in your expression. (line3)

Please note a 'mouse over' of Me!frmSubProjects.Form!Project in the above code does show a result (first 'Project' value in subform) - so what am i doing wrong??

thanks in advance,
Paul

 

[RuralGuy]

In SQL statements you need to reference through the Forms collection:
strSQL = strSQL & " Forms.MainFormName!frmSubProjects.Form!Project = 2 "

 

[Paul_B]

thanks RuralGuy,

I'm almost there. that fixed the error, however I'm now getting the same error on the next line? I've changed it to:

Forms.frmLookupMaint!frmSubProjects.Form!RecordSource = strSQL

a mouse over of the above does reveal that the recordsource = "tasks" which is correct.

where to now?

thanks again,
Paul.

 

[RuralGuy]

Sorry it took so long to get back. Got knee deep in a project. I think your original syntax should work:
Me!frmSubProjects.Form.RecordSource = strSQL