Filter subform data from main form combo box(s)

[serviceman via AccessMonster.com]

Hi gang,
One more little problem to work here:
I have a form 'formadmintestmain' with 3 tab pages on it. On one of these
pages is a subform 'formadmintestpage1' which gets its' data from a query in
the properties sheet. When I open the main form and select a test to
administer, the subform shows all of the students in attendance for that test.
What I would like to do is be able to filter the students in the subform
using a pair of combo boxes on the main form against one of the fields in the
subform query. The field I want to filter on is RANK_ID. I tried this:

Dim FiltVal As String

FiltVal = Me.Combo33.Value

Me.formadmintestpage1.Form.Filter = "RANK_ID='" & FiltVal & "'"
Me.formadmintestpage1.Form.FilterOn = True

in the after_update field to pass a single selection filter in, but I keep
getting 'error 107: the prefix formadmintestpage1 is not a valid table name'
error. I am using SQL server on this project, and I think the problem is that
I am trying to pass the filter in an MSAccess format. I am truly stuck. I
would like to be able to filter a range BETWEEN using two combo boxes; I
assume I will also have to account for when one or the other box is left NULL.
Help!
Andy

 

[serviceman via AccessMonster.com]

Ok,
An Update. I went another route, and this is starting to work:
Private Sub Combo39_AfterUpdate()
Me.Text43 = DLookup("RANK_DESC", "BELT_RANK", "RANK_ID=" & Me.Combo39)
strSQL = "SELECT ATTENDANCE.STUDENT_ID, ATTENDANCE.EVENT_ID,ATTENDANCE.
LOCATION_ID, ATTENDANCE.EVENT_DAT, ATTENDANCE.comment,ATTENDANCE.EVENT_FEE,
STUDENTS.FST_NAM, STUDENTS.LST_NAM, TESTRESULTS.KI_BON, TESTRESULTS.IL_JANG,
TESTRESULTS.E_JANG, TESTRESULTS.SAM_JANG, TESTRESULTS.SA_JANG, TESTRESULTS.
OH_JANG, TESTRESULTS.YUK_JANG, TESTRESULTS.CHIL_JANG,TESTRESULTS.PAL_JANG,
TESTRESULTS.GO_RYO, TESTRESULTS.KUMKANG, TESTRESULTS.TAEBEK, TESTRESULTS.
PYUNG_WON,TESTRESULTS.SHIP_JIN , BELT_RANK.RANK_DESC, BELT_RANK.RANK_ID FROM
ATTENDANCE INNER JOIN STUDENTS ON ATTENDANCE.STUDENT_ID = STUDENTS.STUDENT_ID
LEFT OUTER JOIN BELT_RANK ON STUDENTS.BELT_RANK = BELT_RANK.RANK_ID LEFT
OUTER JOIN TESTRESULTS ON ATTENDANCE.STUDENT_ID = TESTRESULTS.STUDENT_ID AND
ATTENDANCE.EVENT_ID = TESTRESULTS.EVENT_ID AND ATTENDANCE.LOCATION_ID =
TESTRESULTS.LOCATION_ID AND ATTENDANCE.EVENT_DAT = TESTRESULTS.EVENT_DAT
where"
strSQL = strSQL & " RANK_ID BETWEEN " & Me!Combo39 & " AND " & Me!Combo41
Me!formadmintestpage1.Form.RecordSource = strSQL
End Sub

sorry for the long query... anyway, this will filter the subform ok, with a
few quirks. I have the same event in the after_update of combo41; When I
first open the main form and set a value in Combo39 I get an error because
combo41 is NULL. I need to figure out how to handle that. Any ideas would be
helpful!
andy