Opening records via Double-Click event on a search form.

[IncidentalProgrammer]

Currently, I have two search forms in my database, and will be making more in the future. While I have the actual search function down, I'm struggling in two places, at the moment:



1) Can I make it so that users can click anywhere in a row, have that entire row gain focus, and enable them to open the corresponding form by double-clicking anywhere in the row with the record they want to view?

2) Syntax for a DoCmd.OpenForm where I have two parameters for the "Where". The form I'm working on at the moment is for searching insurance policies. Each policy may have multiple accounts attached to it, and those accounts have to be visible in the search, so multiple entries for each policy are going to appear in the search form (but that's fine). I'm wanting to make it so that a user can click the policy row that also has the account they want to view it with, and have the policy profile form come up with that policy and account. The hard part is that on the policy profile, in order to accomodate for multiple accounts, the account data is shown in a subform (where a combo box on the main form lets users pick which of the associated accounts they want to see in the subform). So I'm thinking the way to do it is to have the account number from the search form move to the combo box on the policy profile form, and that should populate the subform appropriately, but I'm still having a hard time with telling the DoCmd to open the form with the right Policy Number AND Account Number. All the examples I've looked up for syntax help have been for just one.

[IncidentalProgrammer]

Just had a thought! It might help if I provide a visual to show what I mean by selecting an entire row. I'm using "Continuous Forms".

[KyleMac]

I did something very similar. Essentially you'd want to setup the filter on your new form based off the two criteria you are specifying.

This is the form that opens when you double click on the text box:

Private Sub Form_Open(Cancel As Integer)

        Me.MClmNum = Forms!frmClaimSearch.MClmNum.Text 'Copying the value from your record filter to the new form (MClmNum as an example)
        Me.MClmLoanNum = Forms!frmClaimSearch.MClmLoanNum.Text
        Forms!frmClaimSearch.MClmNum.Text = "" 'Clearing it if you used a separate text box to identify the record (I did, not sure if it's necessary)
        Forms!frmClaimSearch.MClmLoanNum.Text = ""

        Dim strMyFilter As String
        Dim lngLen As Long

        If Not IsNull(Me.MClmNum) Then
            strMyFilter = strMyFilter & "([MClmNum] = """ & Me.MClmNum & """) AND "
        End If
        If Not IsNull(Me.MClmLoanNum) Then
            strMyFilter = strMyFilter & "([MClmLoanNum] = """ & Me.MClmLoanNum & """)"
        End If
        
        lngLen = Len(strMyFilter)
        If lngLen <= 0 Then
            strMyFilter = ""
            Me.Filter = strMyFilter
            Me.FilterOn = True
            Exit Sub
        Else
            strMyFilter = Left$(strMyFilter, lngLen)
            Me.Filter = strMyFilter
            Me.FilterOn = True
        End If

     
End Sub

[IncidentalProgrammer]

Thanks, KyleMac!

Forgive me, I am still VERY new to programming and Access. Could I potentially route that into a DoCmd.OpenForm on the search form? There are multiple ways to access these profile forms, so I'm worried that putting it on the profile forms' open event could interfere with opening from another path. Or would that not be the case? I could be wrong.

[June7]

Consider instead of the Filter and FilterOn code:

DoCmd.OpenForm "formname", , , strMyFilter