Filtering Forms

I need to have a form filter from two differnt fields.

First the user needs to be able to pick from a list of categories.
Then the user needs to be able to choose a size from the results of
the category.

If i set the parameters in the query, then they can't choose from the
list.

This is what I came up with, but when you use the second drop down
box, it doesn't pull from just teh results of the first box.

Private Sub Combo1_Change()
Dim strFilter
strFilter = "[VesselCategory]=""" & Combo1.Text & """"
Form_AllJobs.Visible = True
Form_AllJobs.Filter = strFilter
Form_AllJobs.FilterOn = True
Form_AllJobs.Refresh
End Sub



Private Sub Combo32_Change()
Dim strFilter
strFilter = "[Vesselsizerange]=""" & Combo32.Text & """"
Form_AllJobs.Visible = True
Form_AllJobs.Filter = strFilter
Form_AllJobs.FilterOn = True
Form_AllJobs.Refresh
End Sub

Re: Filtering Forms

Rosy wrote:
> I need to have a form filter from two differnt fields.
>
> First the user needs to be able to pick from a list of categories.
> Then the user needs to be able to choose a size from the results of
> the category.
>
> If i set the parameters in the query, then they can't choose from the
> list.
>
> This is what I came up with, but when you use the second drop down
> box, it doesn't pull from just teh results of the first box.
>
> Private Sub Combo1_Change()
> Dim strFilter
> strFilter = "[VesselCategory]=""" & Combo1.Text & """"
> Form_AllJobs.Visible = True
> Form_AllJobs.Filter = strFilter
> Form_AllJobs.FilterOn = True
> Form_AllJobs.Refresh
> End Sub
>
>
>
> Private Sub Combo32_Change()
> Dim strFilter
> strFilter = "[Vesselsizerange]=""" & Combo32.Text & """"
> Form_AllJobs.Visible = True
> Form_AllJobs.Filter = strFilter
> Form_AllJobs.FilterOn = True
> Form_AllJobs.Refresh
> End Sub

I sometimes do something like this. First, I use the AfterUpdate event
but it probably doesn't make any difference...I just like to have it
make it past the NotInList event or any error routines that may exist
before setting the filter.

Private Sub Combo1_AfterUpdate()
'refresh the list in combo 32 to associate with Combo1
Me.Combo32.Requery
SetFilter
ENd Sub
Private Sub Combo32_AfterUpdate()
SetFilter
ENd Sub
Private Sub SetFilter()
Dim strFilter
IF Not IsNull(Me.Combo1) then
strFilter = "[VesselCategory]='" & Me.Combo1 & "' And "
IF Not IsNull(Me.Combo32) then
strFilter = strFilter & _
"[Vesselsizerange]='" & Combo32 & "' And "
Endif
Endif
If strFilter > "" THen
'remove the "And"
strFilter = _
Left(strFilter,len(strFilter)-5)
Endif
Form_AllJobs.Filter = strFilter
Form_AllJobs.FilterOn = (strFilter > "")
End Sub

BristleBot Cat Toy
http://www.youtube.com/watch?v=rUSTXUis_ys

Re: Filtering Forms

On Fri, 18 Apr 2008 08:27:08 -0700, Rosy wrote:

> First the user needs to be able to pick from a list of categories. Then
> the user needs to be able to choose a size from the results of the
> category.

I usually use an OnExit event on the first field to reset the
RecordSource of the second field to the new query that I would build.
You could filter, but I have found that filters can be affected by too
many other things. Setting an new query string would resolve the problem
and limit the list to only the items listed in the query.

If you want a caption as the first list item, then you will have to run
the query by program and load the listbox with your values.