Filter subform

[Kurt Wimberger via AccessMonster.com]

Hi all:

I have a form that displays basic info from a DB. On that form I have a
subform that I want to display related data from another table. The catch
is this: the data will not be related 1 to 1 by a key. The purpose of this
form is to locate keys in subform B that are CLOSE to keys in form A. The
user has a combo box that allows them to select the level of sensitivity
for this search.

So, if the user has selected 3 as the level of sensitivity, and the first
key to come up on form A is: 'AZ95-1234-567', then I want the subform to
show ANY key that is LIKE 'AZ9%'. If the user chose a sensitivity level of
4 then I would search table B for keys LIKE 'AZ95%', and so on.

Rather than run query after query, I thought the best approcah would be to
bind subform B to a query that pulls all keys from the second table. Then,
as the user clicks through form A, the OnCurrent event would trigger a
rs.Filter using the current form A key and a bit of string manipulation to
pull out the number of characters they want to search by. (The sensitivity.)

Okay, it pretty much works. When the main form is first opened you do see
all keys from the second table in subform B. But, for some reason, as soon
as you click to the next record on main form A, the rs.Filter code fires
but nothing appears in the subform. The nav buttons on the subform also
never register a change in the returned/filtered records. They are grayed
out.

What can I be missing here? I return no errors.

Here's the code:
Code:
'-----------------------------------------------------------------------
Option Compare Database
Option Explicit

Private Sub Form_Current()
Call subFindMatch
End Sub

Private Sub cmbSpaces_AfterUpdate()
Call subFindMatch
End Sub

Private Sub subFindMatch()
intSpaces = CInt(Me.cmbSpaces)

MsgBox "intSpaces: " & intSpaces & " and filter = '" & Left(Me.Strain,
intSpaces) & "%' "
'MsgBox "rsType: " & Me.sbfrmMatches.Form.RecordsetType

Me.sbfrmMatches.Form.Filter = "mAccession LIKE '" & Left(Me.Strain,
intSpaces) & "%'"
Me.sbfrmMatches.Form.FilterOn = True
'Me.sbfrmMatches.Form.Refresh

End Sub

Then I tried moving the .Filter code into the subform module itself. I call
that sub from the main form:

Code:
'--- Main Form ---
Private Sub Form_Load()
If ("" & Me.txtValue) = "" Then
'Me.RecordSource.MoveNext
MsgBox "Found a blank."
End If
End Sub

Private Sub Form_Current()
intSpaces = CInt(Me.cmbSpaces)
strFilter = "mAccession LIKE '" & Left(Me.txtValue, intSpaces) & "%'"

Call Me.sbfrmMatches.Form.subFindMatch(strFilter)
End Sub

Private Sub cmbSpaces_AfterUpdate()
intSpaces = CInt(Me.cmbSpaces)
strFilter = "mAccession LIKE '" & Left(Me.txtValue, intSpaces) & "%'"

Call Me.sbfrmMatches.Form.subFindMatch(strFilter)
End Sub


'--- Sub form ---
Public Sub subFindMatch(strFilter As String)
MsgBox "subform: strFilter: " & strFilter
'DoCmd.ApplyFilter , strFilter

Me.Filter = strFilter
Me.FilterOn = True
MsgBox "Count after filter: " & Me.Recordset.RecordCount
End Sub


The sub fires but after the first record in the main form passes (and the
..Filter is called in the sub form) the subform no longer shows any records,
even when there is data that matches.

Kurt

 

[Graham Mandeno]

Hi Kurt

Is your table in an Access/Jet database? If so, then the % wildcard stands
for only a single character. Try using * instead.

 

[Kurt Wimberger via AccessMonster.com]

D'OH! Spot on, Graham! That was the issue.

Thanks a million.

Kurt