Re: Filter Records with Multiple Combo Boxes

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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: MMControl
    ... Use the "stop" MCI command string. ... Private Declare Function mciGetErrorString Lib "winmm.dll" Alias ... Private Sub DisplayMCIError ... Dim sBuffer As String * 255 ...
    (microsoft.public.vb.general.discussion)
  • Move images and lines on a picturebox....
    ... Private Type LINKDATA ... sLabel2 As String ... Dim MyData() As LINKDATA ... Private Sub Form_MouseDown(ByRef Button As Integer, ...
    (microsoft.public.vb.general.discussion)
  • cmd Button, List, Index As Integer
    ... "Private Sub cmdLoadFile_Click" ... If I add "Dim Index as Integer to the procedure, ... cFileName As String * MAX_PATH ...
    (microsoft.public.vb.general.discussion)
  • Re: Generic SendInput samples?
    ... Private Declare Function SendInput Lib "user32.dll" (ByVal nInputs As Long, ... Public Sub MySendKeys(Data As String) ... Dim this As String ... Private Sub ProcessChar ...
    (microsoft.public.vb.general.discussion)
  • Proper use of Interfaces?
    ... Private RH As ResultHeader ... Public ReadOnly Property TitleAs String Implements IResultContainer.Title ... Private Sub Page_Load(ByVal sender As System.Object, ... Public Property RowCount() As String Implements IResult.RowCount ...
    (microsoft.public.dotnet.framework.aspnet)