Question More than two Criteria to filter a SubForm

[Tanya7]

Hi All,
I need to filter a subform based on the values on the mainform.
Criteria are Multiple. The idea is to show the user the records that are already existing in the table.

First two criteria works, stuck with the third one

I.Criteria – Creative(text type)

Private Sub Creative_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "'"
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub

II Criteria – Creative(text type) And Act_ID(numeric type)

Private Sub Act_ID_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "' And Act_ID = " & Act_ID
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub

III Criteria – Creative(text type) And Act_ID(numeric type) And Mth_ID(numeric type)

rivate Sub Mth_ID_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _

? third criteria

Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub


Also, how to split the code into two lines

"Creative ='" & Creative & "' And Act_ID = " & Act_ID

when broken into:

"Creative ='" & Creative & "' _
And Act_ID = " & Act_ID

is automatically saved as:
"Creative ='" & Creative & "'" _
And Act_ID = " & Act_ID " and this generates an error on execution.

Any Help on this!!

Thanks & Regards,

Tanya

 

[pr2-eugin]

Hi Tanya, Try the following code..

Private Sub Mth_ID_AfterUpdate()
    Me.frmCommercialWithDetails_subform.Form.Filter = _
    "Creative ='" & Creative & "'" _
    & " And Act_ID = " & Act_ID _
    & " And Mth_ID = " & Mth_ID 
    Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub

to break a String you should be able to concatenate it back..

 

[Tanya7]

Thanks Paul!!! You made my day...

 

[pr2-eugin]

You are welcome.. Happy to Help.

 

[Tanya7]

Paul, another problem to be solved:

What if I need to filter a Subform(frmCommercialWithDetails_subform) from values from:
3 fields from Mainform(frmCommercial Mainform) and
2 fields from another Subform(frmCommercial Subform)


Filtering from the values on Mainform works perfectly (Thanks to you!!)


How do I change the codes If I have to add more criteria say the field is Dur_ID on form (frmCommercial Subform)


Private Sub Dur_ID_AfterUpdate()
Me.frmCommercialWithDetails_subform.Form.Filter = _
"Creative ='" & Creative & "'" _
& " And Act_ID = " & Act_ID _
& " And Mth_ID = " & Mth_ID _
& " And Dur_ID = " & Dur_ID
Me.frmCommercialWithDetails_subform.Form.FilterOn = True
End Sub


I tried replacing:


Me.frmCommercialWithDetails_subform.Form.Filter = _


WITH


[Forms]![frmCommercial Mainform]![frmCommercialWithDetails_subform].Form.Filter = _

Don't know how to proceed further


Regards,

Tanya

 

[Tanya7]

Got the solution:

Private Sub Dur_ID_AfterUpdate()
Me.Parent![frmCommSubform2].Form.Filter = _
" Creative = '" & Me.Parent![Creative] & "'" _
& "And Act_ID = " & Me.Parent![Act_ID] _
& "And Mth_ID = " & Me.Parent![Mth_ID] _
& "And FY_ID = " & Me.Parent![FY_ID] _
& "And Cl_ID = " & Me.Parent![Cl_ID] _
& " And Dur_ID = " & Dur_ID
Me.Parent![frmCommSubform2].Form.FilterOn = True
End Sub

(to make the coding short, renamed frmCommercialWithDetails_subform as frmCommSubform2)