Filter subform based on combo box selections

X

XLEAccessGuru

XLEGuru
Local time
Today, 09:39
Joined
Nov 17, 2006
Messages
65
Hello all,

I have a form that has three combo boxes. My users will select a value from the drop down that will filter a SUBform on the after update event of the combo. I'm really struggling with how to write code to do this. I currently have a hidden text box that will contain the SQL filter string I want to apply to the subform, but I'm struggling with how to write code that will apply a filter based on combo 1, 2 or three each time they are updated. Does that make sense?

In other words, I want these combo boxes to filter the subform after each after_update event whether they are null or not.

I've seen several posts on this topic and tried many of them, but I'm still struggling because the setups in the examples are somewhat different. Any other suggestions?

Thanks!!!!
 
I have done this with success;

Add a function similar to this to your form
Code: 
Private Sub RunFilter()

    Dim strFilter       As String
    Dim bFilter         As Boolean

bFilter = False
strFilter = ""


If Nz(Me.comPeriod, 0) > 0 Then 'Period
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "PeriodID = " & Me.comPeriod
    bFilter = True
    End If
    
    
If Nz(Me.TargetTypeID, 0) > 0 Then 'TargetType
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "TargetTypeID = " & Me.TargetTypeID
    bFilter = True
    End If
    
If Nz(Me.EmployeePK, 0) > 0 Then 'EmployeePK
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "EmployeePK = " & Me.EmployeePK
    bFilter = True
    End If
    
If Nz(Me.comPortfolio, 0) > 0 Then 'EmployeePK
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "PositionID = " & Me.comPortfolio
    bFilter = True
    End If
    
    
If bFilter Then
    Me.frmPayCalculationsSubForm.Form.OrderBy = ""
    Me.frmPayCalculationsSubForm.Form.Filter = strFilter
    Me.frmPayCalculationsSubForm.Form.FilterOn = True
    Else
    Me.frmPayCalculationsSubForm.Form.FilterOn = False
    End If



End Sub

then in the 'afterupdate' event of each of the combo boxes put a call to the function 'RunFilter'

Finally make the rowsource of every combo box a union query much like this

Code: 
Select 0, "<All>" as eName  From Employee  
	UNION SELECT Employee.EmployeePK, Employee.EmployeeName 
	FROM Employee Where EmployeePK in(Select EmployeePK From PayCalculations)  
	ORDER BY eName;


Let me know how you get on.
 
AWESOME! Thanks Vbark! I was getting there with the filter variable but was having trouble putting it all together to make it work.

I am in training the rest of this week and Mon 1/29, but as soon as I get it all worked out, I'll let you know.

Thanks again!!!

;)
 
At this part I'm getting "You can't assign a value to this object":

Code: 
If bFilter Then
    Me.frmPayCalculationsSubForm.Form.OrderBy = ""
    Me.frmPayCalculationsSubForm.Form.Filter = strFilter
    Me.frmPayCalculationsSubForm.Form.FilterOn = True
    Else
    Me.frmPayCalculationsSubForm.Form.FilterOn = False
    End If

I'm assuming the subform is not being referenced correctly, but I it looks right so I can't figure it out. Any ideas?
 
Did you replace 'frmPayCalculationsSubForm' with the name of your subform?
 
Yes I did. Still getting the message - seems access doesn't like changing a subform's properties....

Any other ideas?
 
Not sure a few things to check.

Exactly which line did it break on?

The subform control has enabled and locked properties they should be enabled = yes and locked = no that seems the most likely for that error message.

Another thing is the subform control doesn't necessarily have the same name as the subform its self, the name in the 'frmPayCalculationsSubForm' part needs to be the name of the control.

Otherwise, paste your runfilter function here and I'll see if I can spot anything

Also you could paste this code into the on load event of your form
Code: 
'-- check output, remove later
    On Error Resume Next
    
    Dim prp As Property
    
    Debug.Print "Form:"
For Each prp In Me.Properties
    Debug.Print "   " & prp.Name & ": " & Nz(prp.Value)
    Next prp
    
Debug.Print "SubForm:"
For Each prp In Me.SubfrmProductRules.Properties
    Debug.Print "   " & prp.Name & ": " & Nz(prp.Value)
    Next prp
'-- check output, remove later

change the name of the subform again

Open the form then go back to the vb editor and paste the results here as well

don't forget to remove the code after use.
 
Yes, I want tips to that problem as well. How can I filter subform with text field value in main form. I've tryied that Me![SubFormName].Form.Filter & FilterOn thing, but that don't work. Any other suggestions?
 
You must log in or register to reply here.

Similar threads

H
Problem with Search Boxes
2
Replies
24
Views
894
Pat Hartman
P
H
Solved Search across multiple fields
Replies
8
Views
569
Harry Paraskeva
H
C
Compile error
Replies
9
Views
363
ChrisLeicester
C
P
Solved Trouble creating a filter on a form displaying records built in a query
Replies
4
Views
448
p_bij
P
nimeshaanethmini
Need Help with VBA Code for Access Form Filtering
Replies
5
Views
813
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom