filtering forms dynamically

accesslearner

Registered User.
Local time
Yesterday, 20:50
Joined
Nov 16, 2010
Messages
38
Can anyone help me in the right direction. I have created 2 filter forms but i am required to build a more complex filter form for the entire database.

I have created a form with Field,operator,value, logic

I am trying to create a 3 level filter option. Narrowing down the search to the records i require. Level 1 filters some records based on a criteria level 2 and if reqd. level 3 narrows down the search with the prefiltered option based on level 1.

I have a snapshot of the form and also adding my database. Form1 is the form i need to filter.

Can anyone point me to some code or the right direction.

Your help is much appreciated.
 

Attachments

  • filtersample1.zip
    filtersample1.zip
    68.3 KB · Views: 105
  • multisearch.jpg
    multisearch.jpg
    52.4 KB · Views: 116
Hello John,

I have followed this example and do have a form by the name Formmulti with the qrySearchAll in the database attached. But they are looking for a more structured 3 layers deep search string that can filter results. I have been working with different combinations. I think with the above the user has to key in and the filter changes with each stroke but they are looking at selection from combo boxes using various dynamic select statements.
Thanks for your prompt response
 
Try changing the recorsource on the subsequent combo boxes using the afterupate() trigger.

pseudocode:

combo1_Afterupdate()
strsource2 = (query based on value in combo1)
combo2.setfocus
combo2.recordsource = strsource2
combo2.requery


Combo2_AfterUpdate()
strsource3 = (query based on value in combo3)
combo3.setfocus
combo3.recordsource = strsource2
combo3.requery

If you are not confortable writing the sql, use a query designer and copy the sql or simply use a saved query as the combo box's source then refresh with code.
 
If you use the criteria;
Code:
Forms!FRM_FormName!ComboName
In the appropriate criteria of the query that is feeding your List box, and then requery the List Box whenever your Combos change, I think you will achieve your goal.
 
I have changed my query that feeds the list box with this.


SELECT tblvariables.ID, tblvariables.variabletxt
FROM tblvariables
WHERE (((tblvariables.ID) Like "*" & [forms]![FRM_SearchMulti]![listsearch] & "*"));


on all my combo onchange event i have added this code

Private Sub cboname_Change()
listsearch.Requery
End Sub

listsearch is the name of my combo

but when i click on the list box i keep getting a popup message enter parameter value and when i press ok i get back to my form.

how to get rid of this
 
You need to use the afterupdate event rather than the onchange because the value you are referencing is not committed while the combo box still has focus and therefore cannot be used as a parameter. It would also be more effecient this way since the user would have to change focus to the next combo box anyway to utilize it either by tabbing or clicking it.

This may be of help with understanding hte order of events:
http://office.microsoft.com/en-us/access-help/order-of-events-for-database-objects-HP005186761.aspx
 
After hours of playing around i have the form filtering but not completely.

I have 2 unbound cascading combo boxes

company name
employee name

when select company name and click on employee name combo box only those employee who are working in the company is highlighted
which is ok

in the company combo i have this code

Private Sub cbocpy_AfterUpdate()
Dim scpySource As String

scpySource = "SELECT [tblcontacts].[companyID], [tblcontacts].[ContactID], [tblcontacts].[NameFull]" & "FROM tblcontacts " & " WHERE [companyID] = " & Me.cbocpy.Value
Debug.Print [scpySource]
cboemp.SetFocus
Me.cboemp.RowSource = scpySource
cboemp.Requery

End Sub



now based on the employee listed in the second combo i want to filter the subform.
Private Sub cboemp_AfterUpdate()
Dim strWhere As String
If Len(Me.cboemp) > 0 Then
strWhere = "([CompanyID] Like ""*" & Me.cboemp & "*"")"
End If
Me.contactsubform.Form.filter = strWhere
Me.contactsubform.Form.FilterOn = True

End Sub

I get filtered data based on company, how to get only the single employee data. I tried changing the where clause to
strWhere = "([ContactID] Like ""*" & Me.cboemp & "*"")"

but am not getting the right filtered results.
any help would be appreciated.
 
A couple of things

1. You don't need to requery the combo if you are setting its row source from code, it will do it automatically whenever you change the row source.

2. Don't use LIKE and wild cards. If you are selecting a company then limit it by that company ID. Same with employee.

Code:
Private Sub cboemp_AfterUpdate()
Dim strWhere As String
 
If Len(Me.cboemp) > 0 Then
   strWhere = "[[B][COLOR=red]ContactID[/COLOR][/B]] =  " & Me.cboemp 
End If
 
Me.contactsubform.Form.filter = strWhere
Me.contactsubform.Form.FilterOn = True
 
End Sub


And make sure you are using the name of the subform CONTROL (control on the main form which HOUSES the subform) and not the name of the subform itself, unless they share the same name.

plus shouldn't it be ContactID that you are looking for here?
 
Last edited:
Thanks Bob for your prompt reply

This is the code i have now
Private Sub cboemp_AfterUpdate()
Dim strWhere As String
If Len(Me.cboemp) > 0 Then
strWhere = "[ContactID] = " & Me.cboemp
End If

Me.contactsubform.Form.filter = strWhere
Me.contactsubform.Form.FilterOn = True
End Sub

The filtered results are not correct.
For a single company employee the data is filtered but for more than one employee i am not getting the right data.

Eg. when for a company A
emp C & D

When i click on company A i have C& D in the combo box
when i select C it gives me employee from another company say X

When i click on X company
i get the previous selected result emp C

Sorry i dont mean to confuse the group here, does this have anything to do with the rowsource which is companyID which is passed to emp combo.

Also bob with all the help from this group i have designed 2 filter forms
1 searches with a text field as you type
2 using a list box and have combo boxes to filter from a sub form

how can i use a combo box with values like name , company state etc using only one combo box.

if my combo box name was cbofilter should the code have all search feilds with the OR between.

If Len(Me.cbofilter > 0 Then
strWhere = "[ContactID] = " & Me.cbofilter "OR" strWhere = "[CompanyID] = " & Me.cbofilter etc..
End If

I have a combo rowsource - value list
Eg. Operator = > <

When we are searching for Budget < 2000

Where Budget is the search feild
< - operator
2000 - value user typed in to filter the records

is there a way the value list can be coded to acheive this result.

Thanks for all your inputs. Through your previous posts i learnt how to use the immediate window and to debug correctly.
 
I have been getting a lot of input and i have played around a lot of options but i am getting no where. I have tried a different approach where i have an sql query as the forms row source and filter the subform.

The filter form works for all the combo

What i want to do is just have a single combo and have all the search fields in that combo and be able to do the same filtering.

Also the moment i introduced a cascading combo the company and employee combo did not filter.

since i am changing the row source of the company combo in the afterupdate and the rowsource of the form is different i am not getting the intented results.

I have attached screenshots of my forms.

This is my code

Option Compare Database

Private Sub cbocpy_AfterUpdate()
Dim scpySource As String

scpySource = "SELECT [qrycontdetails].[companyID], [qrycontdetails].[ContactID], [tblcontacts].[NameFull]" & "FROM qrycontdetails " & " WHERE [companyID] = " & Me.cbocpy.Value
Debug.Print [scpySource]
Me.cboemp.RowSource = scpySource
End Sub

Private Sub cmdSearch_Click()
On Error Resume Next


Dim sCriteria As String
sCriteria = "WHERE CompanyID Is Not Null"

'ExerciseData SearchQry

If Not IsNull(Me.cbocpy) Then
sCriteria = sCriteria & " AND [CompanyName] = " & """" & Me.cbocpy & """"
End If
If Not IsNull(Me.cboemp) Then
sCriteria = sCriteria & " AND [NameFull] = " & """" & Me.cboemp & """"
End If

If Not IsNull(Me.cbotitle) Then
sCriteria = sCriteria & " AND [BCMJobTitle] = " & """" & Me.cbotitle & """"
End If

If Not IsNull(Me.cbostate) Then
sCriteria = sCriteria & " AND [BCMBusinessAddressState] = " & """" & Me.cbostate & """"
End If

If Not IsNull(Me.cbocity) Then
sCriteria = sCriteria & " AND [BCMBusinessAddressCity] = " & """" & Me.cbocity & """"
End If

sSQL = "SELECT [CompanyID],[contactID],[PhoneFaxNumber],[CompanyName],[AddressStreet],[BCMBusinessAddressCountry],[NameFull],[BCMJobTitle],[BCMBusinessAddressState],[BCMBusinessAddressCity] FROM qrycontdetails " & sCriteria & ";"


Forms![Search]![searchsubform].Form.RecordSource = sSQL
Forms![Search]![searchsubform].Form.Requery
Debug.Print [sSQL]
End Sub

Private Sub cmdClear_Click()
Index = Null
cbocpy = Null
cboemp = Null
cbotitle = Null
cbostate = Null
cbocity = Null


End Sub

how to make the cascading combo filter results.
how to eventually have only one combo for all my search.
 

Attachments

  • want_to_acheive.JPG
    want_to_acheive.JPG
    52.8 KB · Views: 103
  • what_I_created.JPG
    what_I_created.JPG
    34.7 KB · Views: 108

Users who are viewing this thread

Back
Top Bottom