Filter with combo box

  • Thread starter Thread starter the-trooper
  • Start date Start date
T

the-trooper

Hi Everyone.
Here is my problem. I know how to make a find control with combo box
and filter with command buttons. But filter with command buttons isn't
practical if you have many groups by which you can filter (you should
have hundreds of buttons an a form).
What I wont to make is a filter with combo box. I wont to select item
from a list and see all records with it, just like I pressed one of
command buttons. Like AutoFilter in Excel. If this is possible to do
with use of only macros I would appreciate it.
I hope you understand what I mean and can help me.

Thanks in advance!
 
Hi Everyone.
Here is my problem. I know how to make a find control with combo box
and filter with command buttons. But filter with command buttons isn't
practical if you have many groups by which you can filter (you should
have hundreds of buttons an a form).
What I wont to make is a filter with combo box. I wont to select item
from a list and see all records with it, just like I pressed one of
command buttons. Like AutoFilter in Excel. If this is possible to do
with use of only macros I would appreciate it.
I hope you understand what I mean and can help me.

Thanks in advance!
Click to expand...

If you have a lot of combo it's not userfriendly to make a filter each
time you select a record in the combo, as the user will each time wait
the query to give the résult.
It's better to allow the user to select the various combos and at the
end have ONE button to filter!

Otherwise find below a code sample to select in a form whan selecting
in a combo :
-------------------------------
You just have to put the chercherEnrChoisiDans function in the onClick
proc of the combo like

sub myCombo_Click()
chercherEnrChoisiDans(activeControl)
end sub

Function chercherEnrChoisiDans(ctl As ComboBox)
On Error GoTo erreur: GoTo debut
erreur:
traiterErreurModule nomModule: Exit Function
debut:
13501:
Dim nomZone As String, frm As Form
Set frm = ctl.Parent
nomZone = nomZoneLieAZoneDeListe(ctl)
13502:
If (frm.RecordsetClone.Fields(nomZone).Type = dbText) Then
frm.RecordsetClone.FindFirst "[" & nomZone & "]='" & Nz(ctl, "") & "'"
Else frm.RecordsetClone.FindFirst "[" & nomZone & "]=" & Nz(ctl, 0)
frm.Bookmark = frm.RecordsetClone.Bookmark
End Function

Function nomZoneLieAZoneDeListe(zdl As Control) As String
On Error GoTo erreur: GoTo debut
erreur:
traiterErreurModule nomModule: Exit Function
debut:
14501:
Dim requete As QueryDef, BDS As Database
Set BDS = CurrentDb
Set requete = BDS.CreateQueryDef("", zdl.RowSource)
nomZoneLieAZoneDeListe = requete.Fields(zdl.BoundColumn - 1).Name
requete.Close
Set requete = Nothing
Set BDS = Nothing
End Function
---------------------
 
Back
Top