Pseudo-Splitform with search box help

[cnstarz]

Need help w/Pseudo-Splitform with search box

I have a parent form that emulates split view (fmTasks, source: tbTasks). In the footer of fmTasks is a subform that is in datasheet view (sbfm_Tasks, source quTasks). Clicking on a record in the subform (On Current event) loads the record in the parent form. All good.

I implemented a search/filter box in the header of the parent form that filters (via On Change event) the query that sbfm_Tasks is based off. This works fine, however, now records won't load in the parent form when I click on them from the subform. Clicking on a record in the subform moves the selection to the first record in the subform which forces the main form to load only that first record. The main form does load the clicked record for about a tenth of a second, but it quickly goes to the first record. Clicking the record navigation buttons in the subform results in the same thing. Can anyone help me out? I've been scouring the interwebs the last 2 days trying different methods of loading the record in the main form to no avail. :banghead:

The code for the On Current event of the subform is this:

Private Sub Form_Current()

    If CurrentProject.AllForms("sbfm_Tasks").IsLoaded = True Then
        Exit Sub
    Else
        Me.Parent.Recordset.FindFirst "Task_ID = " & Me.Task_ID
    End If
    
End Sub

The code for the On Change event of the filter box:

Private Sub txtFilter_Change()

    Dim strFilterText As String
    
    strFilterText = txtFilter.Text
    txtHiddenFilter.Value = strFilterText
    
    Me.sbfm_Tasks.Requery
    
'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box txtFilter
    If Len(Me.txtHiddenFilter) <> 0 And InStr(Len(txtHiddenFilter), txtHiddenFilter, " ", vbTextCompare) Then
        'Set the focus on the first item in the list box
            'Me.SearchResults = Me.SearchResults.ItemData(1)
            'Me.SearchResults.SetFocus
        'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
            'DoCmd.Requery
        'Returns the cursor to the the end of the text in Text Box txtFilter,
        'and restores trailing space lost when focus is shifted to the list box
            Me.txtFilter = strFilterText
            Me.txtFilter.SetFocus
            Me.txtFilter.SelStart = Me.txtFilter.SelLength
            
        Exit Sub
    End If

'Set the focus on the first item in the list box
    'Me.SearchResults = Me.SearchResults.ItemData(1)
    'Me.SearchResults.SetFocus

'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
    'DoCmd.Requery

'Returns the cursor to the the end of the text in Text Box txtFilter
    Me.txtFilter.SetFocus

    If Not IsNull(Len(Me.txtFilter)) Then
        Me.txtFilter.SelStart = Len(Me.txtFilter)
    End If
    
End Sub

 

[cnstarz]

It seems like the subform is doing it's job in displaying the selected record in the main form, but after passing the Task_ID to the main form something is telling the subform to immediately requery/refresh and/or move to the first record of the subform's recordset which forces it to pass the Task_ID of the subform's first record to the main form.

So... Click on subform record -> subform passes Task_ID to main form -> main form loads record with matching Task_ID -> mysteriously, subform immediately requeries/refreshes/goes to first record -> subform once again passes Task_ID to main form -> main form loads record with matching Task_ID.

That's just my guess, I'm obviously no guru or that experienced, and I have no idea why this would be happening. I can't find the culprit.

Edit: Realized some folks can't open accdb files. Here's a zip with both accdb and mdb.

 

[spikepl]

 

[cnstarz]

Ha, filtering works just as well! But why does query criteria cause the subform to reload when a record is selected? I'm trying to wrap my head around the cause of the problem so I can avoid anything similar in the future. Thanks so much!

 

[spikepl]

To be honest - mostly I do not bother figuring out the quirks but just bypass them. In your original code you can get a clue by putting MsgBox "Hello from subform current event" in the ..*drum roll* ... current event of the subform and see what happens. Seemingly the source query having parameters on the main form cause some type of update/reload .. whatever.

In any case you have one more option: make a form and then put two subforms on it. Your current main form (without subform) and, as a separate subform, your current subform. Keep the search fields on the new main form. This ought to separate the events sufficiently to give you the freedom to do whatever you want.

 

[cnstarz]

Thanks Spike, you've been really helpful!

 

[cnstarz]

So far the filter bar is great! But now we're wanting to take it a step further and filter multiple criteria, separated by commas, that can span across multiple fields. For instance, assuming the table below:

[U][B]  ID  | Field A | Field B | Field C [/B][/U]
  1     Cat       Dog       Bird
  2     Earth     Rock      Fire
  3     Rain      Wind      Water
  4     AA Cat    BB Fire   CC Rock

Typing in "r" will result in all of the records being there:

[U][B]  ID  | Field A | Field B | Field C [/B][/U]
  1     Cat       Dog       Bi[B][COLOR="Blue"]r[/COLOR][/B]d
  2     Ea[B][COLOR="blue"]r[/COLOR][/B]th     [B][COLOR="blue"]R[/COLOR][/B]ock      Fi[B][COLOR="blue"]r[/COLOR][/B]e
  3     [B][COLOR="blue"]R[/COLOR][/B]ain      Wind      Wate[B][COLOR="blue"]r[/COLOR][/B]
  4     AA Cat    BB Fi[B][COLOR="blue"]r[/COLOR][/B]e   CC [B][COLOR="blue"]R[/COLOR][/B]ock

Typing in "ro" will result in:

[U][B]  ID  | Field A | Field B | Field C [/B][/U]
  2     Earth     [B][COLOR="Blue"]Ro[/COLOR][/B]ck      Fire
  4     AA Cat    BB Fire   CC [COLOR="blue"][B]Ro[/B][/COLOR]ck

Typing in "rock, fire" will result in:

[U][B]  ID  | Field A | Field B | Field C [/B][/U]
  2     Earth     [B][COLOR="Blue"]Rock[/COLOR][/B]      [COLOR="blue"][B]Fire[/B][/COLOR]
  4     AA Cat    BB [B][COLOR="blue"]Fire[/COLOR][/B]   CC [COLOR="blue"][B]Rock[/B][/COLOR]

Typing in "rock, fire, a cat" will result in:

[U][B]  ID  | Field A | Field B | Field C [/B][/U]
  4     A[COLOR="Blue"][B]A Cat[/B][/COLOR]    BB [B][COLOR="blue"]Fire[/COLOR][/B]   CC [COLOR="blue"][B]Rock[/B][/COLOR]

Typing in "rock, fire, b cat" will result in empty results since no field contains "b cat".

[U][B]  ID  | Field A | Field B | Field C [/B][/U]

So, commas would separate criteria and only records that contain all criteria would be displayed. Does this make sense?