Filtering SubForm 1

[pvje]

in a combo box, one can type in a letter or a group of letters and the cursor "goes to that line" for selection if it finds a match on the beginning letters. For example you have a list of "contacted", "no contacts available", "not contacted". If you type in "no", the cursor goes to the "no contacts available", if you the ntype in the "t", it goes to "not contacted", etc.

I have a main form with a continuous subform. Once I populate the subform is it possible, using the recordset already retrieved, to use a method similar to this? At this point the records have already been prefiltered from selections on the main form, but sometimes the list is still long and they want to got the "L's" to see whats there and then go back to the "C's" to see whats there. I know I can put more filters on the Main form but this would be easier and faster for them. Is the above possible?

Thanks

 

[TheAceMan1]

How are ya pvje . . . . .

Yes its possible. This is what you need to do (Note: in any code, [blue]you![/blue] substitute proper names in [purple]purple[/purple]):
[ol][li]Make a query for [blue]your subform[/blue] (not SQL!) that performs your prefilter using criteria (as if the query were the recordsource instead). For tracking purposes lets name it [blue]qryPreFilter[/blue][/li]
[li]In design view of the subform, [blue]add an unbound textbox.[/blue] This is where the user will type in the final character by character filter. Lets name the textbox [blue]txtSearch[/blue].
Remove whatever is in the RecordSource.[/li]
[li]In a [blue]module[/blue] in the [blue]modules window[/blue], copy/paste the following function:

[blue]Public Function qSearch()
   qSearch = Forms![purple][b]MainformName[/b][/purple]![purple][b]SubformName[/b][/purple].Form![b]txtSearch[/b] & ""
End Function[/blue]

The functions is [blue]responsible for passing [blue]txtSearch[/blue] to another query[/blue] you'll make (the final filter, which follows).[/li]
[li] Start a new query in design view, only [blue]don't select any tables[/blue], goto the query tab and select [blue]qryPreFilter[/blue].

Put all the fields in [blue]qryPreFilter[/blue] in the query grid.

In the criteria for the field you'll use to filter, copy/paste the following:

[blue]Like qSearch() & "*" Or qSearch() Is Null[/blue]

Save & name the query [blue]qryFullFilter[/blue][/li]
[li]Open the subform is [blue]design view[/blue] and in the [blue]OnOpen[/blue] event copy/paste the following:

[blue]   Me.RecordSource = "[b]qryFullFilter[/b]"[/blue]

Then in the OnChange event of [blue]txtSearch[/blue] copy/paste the following:

[blue]   Me![purple][b]AnyOtherTextbox[/b][/purple].SetFocus [green]'Update txtSearch control[/green]
   Me.Requery [green]'resordset updates per txtSearch.[/green]
   
   [green]'Reposition cursor to continue typing.[/green]
   Me![b]txtSearch[/b].SetFocus
   Me![b]txtSearch[/b].SelStart = Len(Me!txtSearch & "")[/blue]

[/li][/ol]
If you've made it this far . . . thats it. Give it a whirl and report any problems . . .

See Ya! . . . . . .