Problem opening a form with a filter

[PRMiller]

Greetings,

Having a bit of a problem passing a filter from one form to another. I am returning a preview, if you will, of a large recordset to a form I call "frm_Search_Results". The user selects a record by clicking a > button. The button is supposed to set a filter, close the form, and open another form ("frm_Dictionary_Record") to the appropriate record.

I have done all of this in the past with several different databases, but this time it is not working. There is a difference: in the past, each of my search result forms would open a different destination form, with that form being bound to a table.

This time, I wanted to save some space. I created an unbound "frm_Dictionary_Records" and, based on a global variable, the forms fields are populated.

Here's the code:

First, a user selects the type of search they want to do. In this case, the user has selected "Sites" from a previous menu, where a global variable ("gbl_Menu_ID") of 1 is assigned. That value is passed to my "frm_Search_Results" field. Results are queried so:

Private Sub Form_Open(Cancel As Integer)

    Me.cmdSearchAgain.Visible = False

    If gbl_Menu_ID = 1 Then
        Me.RecordSource = "SELECT numSite_pk, strSite_ID, strSite_Name" & _
                          " FROM dbo_tbl_Dictionary_Sites"
                          
        Me.txtIdentifier.ControlSource = "numSite_pk"
        
        Me.lblFieldOne.Caption = "Site ID"
        Me.lblFieldTwo.Caption = "Site Name"
        Me.lblFieldThree.Visible = False
        Me.lblFieldFour.Visible = False
        
        Me.txtFieldOne.ControlSource = "strSite_ID"
        Me.txtFieldTwo.ControlSource = "strSite_Name"
        Me.txtFieldThree.Visible = False
        Me.txtFieldFour.Visible = False
        
        Me.OrderBy = "strSite_ID"

There are, of course, many other "elseif" statements, but I'm clipping the code for readability. This form works fine.

Next, the user sees a record they want some detail on. They click the > ("cmdView") button. Here's that code, still on the same form:

Private Sub cmdView_Click()

    Dim strDocName As String
    Dim strLinkCriteria As String

    If gbl_Menu_ID = 1 Then
        strDocName = "frm_Dictionary_Record"
        strLinkCriteria = "[numSite_pk]= '" & me![numSite_pk]
    End If
    
    DoCmd.Close
    DoCmd.OpenForm strDocName, , , strLinkCriteria

End Sub

The filter should be applied and the next form opened.

The problem is, no matter which record the user picks -- whether it be the first, middle, or last in the recordset -- my "frm_Dictionary_Record" (the detail of that preview) always opens to the first record in the table.

Here's the form_open code of "frm_Dictionary_Record":

Private Sub Form_Open(Cancel As Integer)
    
    If gbl_Menu_ID = 1 Then
        Me.RecordSource = "SELECT numSite_pk, strSite_ID, strSite_Name" & _
                          " FROM dbo_tbl_Dictionary_Sites"

        Me.txtIdentifier.ControlSource = "numSite_pk"
        
        Me.lblFieldOne.Caption = "Site ID:"
        Me.lblFieldTwo.Caption = "Site Name:"
        Me.lblFieldThree.Visible = False
        Me.lblFieldFour.Visible = False
        Me.lblFieldFive.Visible = False
        Me.lblFieldSix.Visible = False
        
        Me.txtFieldOne.ControlSource = "strSite_ID"
        Me.txtFieldTwo.ControlSource = "strSite_Name"
        Me.txtFieldThree.Visible = False
        Me.txtFieldFour.Visible = False
        Me.txtFieldFive.Visible = False
        Me.txtFieldSix.Visible = False
    End If
               
End Sub

The only way I've gotten this to work is to create another global variable, assign it the value of txtIdentifier back on "frm_Search_Results", then filtering the recordset on the form that way.

So, instead of

    If gbl_Menu_ID = 1 Then
        Me.RecordSource = "SELECT numSite_pk, strSite_ID, strSite_Name" & _
                          " FROM dbo_tbl_Dictionary_Sites"

        Me.txtIdentifier.ControlSource = "numSite_pk"
        
        Me.lblFieldOne.Caption = "Site ID:"

<snip>

the form_open event would read

    If gbl_Menu_ID = 1 Then
        Me.RecordSource = "SELECT numSite_pk, strSite_ID, strSite_Name" & _
                          " FROM dbo_tbl_Dictionary_Sites" & _
                          " WHERE numSite_pk = " & gblTEST & ";"

        Me.txtIdentifier.ControlSource = "numSite_pk"
        
        Me.lblFieldOne.Caption = "Site ID:"

where gblTEST is that new variable.

Needless to say, this is very frustrating. I would rather not stick that variable in there, as it seems to me the filter should work.

Cannot a form be built on a query as well as a table? Does binding a form to a query in effect overrule a filter passed from another form?

Thanks,
Paul

 

[PHV]

I guess that when the form is launched it is unbound, so the criteria parameter of the OpenForm method should be ignored.
You may consider OpenArgs

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PRMiller]

PHV, you are correct (as always) -- the parameter was ignored. Thanks for the reply, though!