Trouble filtering the values of a combo box

[HLCruz via AccessMonster.com]

I have a combo box in a subform that I would like to filter based on the
value of a text field [Route] within that subform. My combo box is based on
a table that has [Route] and [PickUpDate] allowing for multiple pickup dates
for each route. I would like for my combo box to only show the pick up dates
that are available for each addresses route.

This is the code I am currently using ... however, it is not filtering the
combo box and I still see all possible Routes. Am I missing something? Or
am I going about this in the wrong way?

Private Sub cboRouteCode_AfterUpdate()

Dim strWhere As String
If Not IsNull(Me.cboRouteCode) Then
strWhere = "[Route] = """ & Me.cboRouteCode & """"
Me.Filter = strWhere
Me.FilterOn = True
End If


End Sub

Thank You!

 

[Jeff Boyce]

Have you tried building a query that returns the 'filtered' recordset? I
believe you could use a query that points to the textbox for a selection
criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[HLCruz via AccessMonster.com]

Thanks to both of you for responding ... I did end up finding a solution that
works and I'll post it here for future use ...

I created a query with the following SQL ...

SELECT tblRoutes.RouteCode, tblRoutes.RouteDate
FROM tblRoutes
WHERE (((tblRoutes.RouteCode)=[Forms]![Form1]![sysqryScheduledPickUpSubform
subform]![Route]));

and referenced at query in the RowSource

Have you tried building a query that returns the 'filtered' recordset? I
believe you could use a query that points to the textbox for a selection
criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a combo box in a subform that I would like to filter based on the
value of a text field [Route] within that subform. My combo box is based

[quoted text clipped - 22 lines]

Thank You!