Form with Dynamic Recordsource AND Filtered Based on Combobox

[somthinboutmary]

I have different search forms (based on different divisions of our company) where you type a PO Number into a combobox and hit a command button that sets off the following event:
Private Sub PO_SEARCH_Click()
On Error GoTo Err_PO_SEARCH_Click
Dim stDocName As String
Dim stLinkCriteria As String
stLinkCriteria = "[PO_NO]=" & "'" & Me![PO_NO] & "'"

stDocName = "frm_PO_MASTER_CHKLST"
DoCmd.OpenForm stDocName, , , , , , Me.Name
Exit_PO_SEARCH_Click:
Exit Sub
Err_PO_SEARCH_Click:
MsgBox Err.Description
Resume Exit_PO_SEARCH_Click
End Sub

Upon opening the form "frm_PO_MASTER_CHKLST", the following event occurs:


Private Sub Form_Open(Cancel As Integer)
'Sets recordsource depending upon source form
Dim stLinkCriteria As String
Select Case Me.OpenArgs
Case "frm_K_PO_SEARCH"
Me.RecordSource = "qryK_PO_MASTER_HEADER"
Case "frm_A_PO_SEARCH"
Me.RecordSource = "qryA_PO_MASTER_HEADER"
End Select
End Sub


It works as far as the dynamic record sources, but it does not work on filtering the PO number. Can anybody tell me what is wrong with my coding? I know that combining the tables would be the ideal solution, but that is impossible as there are different divisions of our company that have to stay separate. Also, I do not want to duplicate the form, because there will end up being about 10 of them (which would be a huge pain when they all need revisions). Please be kind as I have taught myself Access, and am by no means a pro at it.
Thanks!
Mary

[JeffChr]

I think you should set your stLinkCriteria like this: stLinkCriteria = "[PO_NO] = " & Me![PO_NO]
Then you need to open the form with your filter like this: DoCmd.OpenForm(stDocName,,,stLinkCriteria,,,Me.Nam e)

After resetting your record source you may need to re-apply the filter in your form with a Me.Filter True statement after the Me.RecordSource = "xxx" statement.

I haven't tested my comments.

[somthinboutmary]

I am receiving a compile error: syntax error when I changed to your statement: DoCmd.OpenForm(stDocName,,,stLinkCriteria,,,Me.Nam e). So I took out the ( and ), and then it gave me an error on the Me.Filter. Would you be able to elaborate on what should go there?

Your help is much appreciated!

[JeffChr]

It would probably work if you corrected my typo at the end: Me.Name not Me.Nam e
If that doesn't fix it, remove the parentheses. If that doesn't fix it code it this way:

DoCmd.OpenForm FormName:=stDocName, wherecondition:=stLinkCriteria, OpenArgs:=Me.Name

[ssanfu]

I don't think the Where clause will work because you are changing the record source after the form is opened. (but I could be wrong )

I have never seen this particular method to open a form.
I took a different path:

'button event code
Private Sub PO_SEARCH_Click()
    On Error GoTo Err_PO_SEARCH_Click
    Dim stDocName As String
    Dim stLinkCriteria As String

    If Len(Trim([PO_NO] & "")) > 0 Then
        stLinkCriteria = "[PO_NO]=" & "'" & Me![PO_NO] & "'"

        stDocName = "frm_PO_MASTER_CHKLST"
        DoCmd.OpenForm stDocName, , , , , , Me.Name & "/" & stLinkCriteria
        
    Else
        MsgBox "Please enter a valid PO number!"
    End If
    
Exit_PO_SEARCH_Click:
    Exit Sub
Err_PO_SEARCH_Click:
    MsgBox Err.Description
    Resume Exit_PO_SEARCH_Click

End Sub


'Upon opening the form "frm_PO_MASTER_CHKLST", the following event occurs:
Private Sub Form_Open(Cancel As Integer)
    '    Dim stLinkCriteria As String
    Dim ar  'zero based 1 dimensional array to split the openArgs

    ar = Split(Me.OpenArgs, "/")
    '    MsgBox ar(0)
    '    MsgBox ar(1)

    'Sets recordsource depending upon source form
    Select Case ar(0)
        Case "frm_K_PO_SEARCH"
            Me.RecordSource = "qryK_PO_MASTER_HEADER"
        Case "frm_A_PO_SEARCH"
            Me.RecordSource = "qryA_PO_MASTER_HEADER"
    End Select

    'filter records to PO number
    Me.Filter = ar(1)
    Me.FilterOn = True

End Sub

[JeffChr]

You might be right. I think the filter property is inherited from the record source.

[somthinboutmary]

Thank you Thank you THANK YOU!!!! I should have came here for answers first. Steve's coding worked. Thanks for your help too Jeff! I owe you both my firstborn.

[ssanfu]

You're welcome....
OK, but could we start out with a date or just a drink ??


And welcome to the forum.......

[somthinboutmary]

HaHA! My firstborn is 23...and a Marine. Not sure how he would feel about me setting him up with you.

[ssanfu]

......Now that was funny!!

OK a with you and a beer for him.


And a big thanks for his (and your) service to our country!