Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Turn off filter and use form's finder combo

Turn off filter and use form's finder combo

Turn off filter and use form's finder combo

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.

CODE -->

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

CODE -->

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.

CODE -->

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))

    Exit Sub
    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?

RE: Turn off filter and use form's finder combo

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
        Set rs = Me.RecordsetClone
        rs.FindFirst "[ID] = " & Args
        If rs.NoMatch Then
            MsgBox "Not found: Contact Your App Administrator?"
            '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.


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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close