Re: Filter Records with Multiple Combo Boxes

Thanks for the advice TC. Everything seems to work correctly up to the point
where the filter is actually applied - then I get a run-time error.

I left everything the same except the "ReFilter()" procedure, which I
entered as follows (keeping with your example):

Private Sub ReFilter()
Dim s As String
If gThis <> "" Then
s = " AND ([This]= """ & gThis & """)"
Else
If gThat <> "" Then
s = s & " AND ([That]= """ & gThat & """)"
End If
End If

If s = "" Then
Me.FilterOn = False
Else
Me.Filter = Mid$(s, 6)
Me.FilterOn = True
End If
End Sub

I think everything works correctly until the "Me.Filter = Mid$(s, 6)" line.
When I check the value of 's' in the Immediate window just before stepping
into the Me.Filter line, the following is returned:
And ([This]= "selection")
If I check the value of 's' afterwards (just before the Me.FilterOn
execution) the value is still the same as above. Then I get a prompt to
supply a parameter for [This] followed by a run-time error.

Any ideas?

Jason
--
J. Mullenbach


"TC" wrote:

> I'd do it like this. Change the control/field names to suit.
>
> (untested)
>
> ' module level declarations:
> private gThis as string
> private gThat as string
> private gTother as string
>
> Private Sub This_AfterUpdate()
> gThis = trim$(me![This])
> ReFilter
> End Sub
>
> Private Sub That_AfterUpdate()
> gThat = trim$(me![That])
> ReFilter
> End Sub
>
> Private Sub Tother_AfterUpdate()
> gTother = trim$(me![This])
> ReFilter
> End Sub
>
> private sub ReFilter()
> dim s as string
> if gThis <> "" then s = " AND ([This]=""" & gThis & """)"
> if gThat <> "" then s = s & " AND ([That]=""" & gThat & """)"
> if gTother <> "" then s = s & " AND ([Tother]=""" & gTother & """)"
> if s = "" then
> me.filteron = false
> else
> me.filter = mid$( s, 6)
> me.filteron = true
> endif
> end sub
>
> The abivge is a useful technique for building criteria strings when you
> do not know how many of the criteria (if any) have been set. Note how
> even the first string is given a leading " AND ". Thus, if the string s
> is not empty, you know for sure that it starts with the 5 characters "
> AND ", regardless of which criteria were or were not present - so you
> just strip0 those leading characters, and you are left with a proper
> boolean expression for use in the Filter prop.
>
> HTH,
> TC
>
>
.

Relevant Pages

  • Re: Playing AVI and MPEG using MCI
    ... "mciSendStringA" (ByVal lpstrCommand As String, ... Dim mlRet As Long ... Private Sub CenterObject ... If mlRet 0 Then ...
    (microsoft.public.vb.controls)
  • RE: Adding Bound Pictures to an Access Database
    ... when the student's picture is missing it is leaving the ... Private Sub Form_Current ... Dim strPath As String ... Private Function pfValidFile(aFile As String) As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • RE: jpgs not showing on forms
    ... Rather than embed the pictures in the database store the paths to the JPEG ... Private Sub cmdAddImage_Click ... Dim strAdditionalTypes As String, strFileList As String ... Private Sub cmdDeleteImage_Click ...
    (microsoft.public.access.gettingstarted)
  • Re: how to digitally sign XML documents step by step
    ... Add an XML node. ... >Private strPath As String ... >'The ControlID for the first control you add will be 1. ... >Private Sub ISmartDocument_PopulateActiveXProps(ByVal ControlID As Long, ...
    (microsoft.public.word.vba.general)
  • Re: how to digitally sign XML documents step by step
    ... Private strPath As String ... 'The ControlID for the first control you add will be 1. ... Private Sub ISmartDocument_PopulateActiveXProps(ByVal ControlID As Long, ... Dim strImage As String ...
    (microsoft.public.word.vba.general)