form1 Command Button to open form2 and filter subform

[DSGF]

Hi all,

I want a command button in form#1 to open form#2 and filter it's subform.

My form#1 [Reps_by_Zip] Command Button already opens form#2[Lut_Region] and filters it correctly, but I want it to filter form#2's subform [Lut_Employee] as well.
There are several of these Buttons on form#1 and each one will filter form#2 differently. I just need to figure out one of them..

Current OnClick code is:

Private Sub Lut_Person_Click()
On Error GoTo Err_Command37_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Lut_Region"
    stLinkCriteria = "[Region]=" & "'" & Me![Region] & "'"
 
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command37_Click:
    Exit Sub

Err_Command37_Click:
    MsgBox Err.Description
    Resume Exit_Command37_Click
    
End Sub

I know that if my Command Button was in [Lut_Region](form#2) my OnClick could simply say:

'With Me.Lut_Employee.Form
'.Filter = Lookup_Sales__TeamID.Sales_Team = "Resi Sales / Accts"
'.FilterOn = True
'End With

Here are my thoughts but none have worked yet and I'm not sure which to concentrate on.

I could set the Lut_Region.TAG to my filter info and then pass it to my subform filter...

I could pass a variable from the original Command Button to form#2 and use it to determine the filter...or..

... I believe I should be able to do it all from the Command Button on the original form.

I could't make any of these work....

Newbe programmer. Any help is greatly appreciated.

 

[TheAceMan1]

How are ya DSGF . . .

Try this:

[blue]Private Sub Lut_Person_Click()
   Dim Cri As String, sfrm As Form

   Cri = "[Region]=" & "'" & Me![Region] & "'"
   DoCmd.OpenForm "Lut_Region", , , Cri
   DoEvents [green]'Allow time for form to open[/green]
   
   Set sfrm = Forms!Lut_Region![purple][b][i]subFormName[/i][/b][/purple].Form
   Cri = "[purple][b][i]your subform criteria[/i][/b][/purple]"
   sfrm.Filter = Cri
   sfrm.FilterOn = True
   Set sfrm = Nothing
   
End Sub[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[DSGF]

TheAceMan1,
Your code makes perfect sense.
Thanks very much for your help.
DSGF