Filter subform based on combo box selections (1 Viewer)

X

XLEAccessGuru

XLEGuru
Local time
Today, 05:15
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!!!!
 
V

Vbark

Registered User.
Local time
Today, 21:15
Joined
Jan 23, 2007
Messages
29
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.
 
X

XLEAccessGuru

XLEGuru
Local time
Today, 05:15
Joined
Nov 17, 2006
Messages
65
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!!!

;)
 
X

XLEAccessGuru

XLEGuru
Local time
Today, 05:15
Joined
Nov 17, 2006
Messages
65
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?
 
V

Vbark

Registered User.
Local time
Today, 21:15
Joined
Jan 23, 2007
Messages
29
Did you replace 'frmPayCalculationsSubForm' with the name of your subform?
 
X

XLEAccessGuru

XLEGuru
Local time
Today, 05:15
Joined
Nov 17, 2006
Messages
65
Yes I did. Still getting the message - seems access doesn't like changing a subform's properties....

Any other ideas?
 
V

Vbark

Registered User.
Local time
Today, 21:15
Joined
Jan 23, 2007
Messages
29
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.
 
I

IpeXeuS

Registered User.
Local time
Today, 12:15
Joined
Nov 23, 2006
Messages
98
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

D
Filter Combo based on checkbox with null value
Replies
3
Views
258
theDBguy
theDBguy
H
Solved Trouble Refreshing cbo box upon not in list event
Replies
2
Views
229
HillTJ
H
G
Applying Filter causes odd runtime error
Replies
7
Views
218
Josef P.
J
S
Solved Sub procedure to filter different textboxes in subform
Replies
5
Views
264
silentwolf
S
N
Solved Populating a lazy combo box
Replies
5
Views
331
nector
N

Users who are viewing this thread

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