Turn off filter and use form's finder combo

[TrekBiker]

I'm using a form with a subform in datasheet view to show a set of contracts.

A button on the main form opens a Customers form for the currently selected company in the subform.

Private Sub cmdShowContract_Click()
    Dim SelectedID As Long
        SelectedID = Me.sfmLedger.Form!CustomerID
    'Open the customer form and pass the ID to the .OpenArgs
    DoCmd.OpenForm "frmContracts", acNormal, , , acFormPropertySettings, acWindowNormal, SelectedID
End Sub

The Customers form has this On Load procedure

Private Sub Form_Load()
    With Me
        If Not IsNull(.OpenArgs) Then
            .Filter = "[CustomerID]=" & .OpenArgs
            .FilterOn = True
        End If
    End With
End Sub

This works properly. Still in the Customer form I'd then like to be able to select and go to another customer's record. If I try to turn off the filter in the search combo's AfterUpdate event the record for the first Customer opens, not the one selected one's.

Private Sub Combo10_AfterUpdate()
On Error GoTo Combo10_AfterUpdate_Err
    If Me.FilterOn = True Then
        Me.FilterOn = False
    End If

    DoCmd.SearchForRecord , "", acFirst, "[CustomerID] = " & Str(Nz(Screen.ActiveControl, 0))

Combo10_AfterUpdate_Exit:
    Exit Sub
Combo10_AfterUpdate_Err:
    MsgBox Error$
    Resume Combo10_AfterUpdate_Exit
End Sub

But doing the customer selection a second time works properly. How can I avoid this extra step?

 

[misscrf]

Instead of using the form filter and filtering the recordset, pass the open args and find the record in the full recordset, like this:

Dim rs As DAO.Recordset
    Dim Args As Integer
    
    Args = Nz(Me.OpenArgs, 0)

    If Args = 0 Then
        DoCmd.GoToRecord acDataForm, "frmCustomers", acFirst
    Else
        Set rs = Me.RecordsetClone
        rs.FindFirst "[ID] = " & Args
        If rs.NoMatch Then
            MsgBox "Not found: Contact Your App Administrator?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If

That way, you don't have to turn the form filter on and off. You can just find the record you want, in the recordset. I use the same code to hand a combo drop down for jumping to a record, as well. You just change the rs.FindFirst "[ID] = " & Args to be rs.FindFirst "[ID] = " & me.cbojumptorec.column(0) provided the id is the first column of the combo's rowsource.

misscrf

It is never too late to become what you could have been ~ George Eliot