Apply filter to subform (1 Viewer)

[htadis]

good day !

need your kind assistance for following.

i have a form and subform. need to do some filtering while form is loaidng. for that i put following code in main for "on load" event.

Private Sub Form_Load()
Dim disCon As String
disCon = "Select * from tbl_Impts_main where ((disConfirm) = 0 )"
Me.disconSub.Form.RecordSource = disCon
Me.disconSub.Form.Requery
End Sub

that works fine.

after load above i need to do one more filtering by using a combo box selection which is in the form header of the main form. for that i put below mentioned code. but while executing below given run time error pop ups.


Private Sub Combo4_AfterUpdate()
Dim Voy As String
Voy = "Select * from tbl_Impts_main where ((Voyage) = '" & Me.Combo4 & "')"
DoCmd.ApplyFilter Voy
Me.disconSub.Form.Requery
End Sub

Run time error '2491'
The action or method is invalid because the form or report isn't bound to a table or query.

when click on debug, docmd.applyfilter voyage highlighted in yellow.

Which mistake i have made ?

can i do this both filtering by using VBA and form/subforms ?

 

[CJ_London]

it is not clear whether you are wanting to filter the subform from the original filtered recordset or have a completely new recordset.

If a new recordset then the bit in red

Voy = "Select * from tbl_Impts_main where ((Voyage) = '" & Me.Combo4 & "')"
DoCmd.ApplyFilter Voy
Me.disconSub.Form.Requery

is replaced with this
Me.disconSub.Form.RecordSource = voy

If on the other hand you are filtering the subform further then you can do one of two things.

you can set the subform linkchild and linkmaster properties
linkchild - voyage
linkmaster - combo4

or you set the filter property

Me.disconSub.Form.filter="Voyage = '" & Me.Combo4 & "'"
Me.disconSub.Form.filteron=true

 

[htadis]

CJ London,

many thanks and sorry for the confusion. I need to use same recordset, means out of the data filtered when the form is loading, further selection need to be done by using the combo box.

Shall follow below given yr steps and revert with the out come.

thanks !

 

[htadis]

CJ London,

Done ! Many many thanks. I was struggling with this almost a week.

thanks again.

 

[gashokkumar]

Hello,

Can anyone help on this filtering the forms, I'm stuck here from last week. I was trying to pull the data on forms by using Docmd.applyfilter. Following is the code, which is working for one condition, but when use it for multiple the form is appearing the last record after the filter.

Single Filter working fine:
DoCmd.OpenForm "Auditor Form"
DoCmd.ApplyFilter , "Processor='Alex'"

Multiple filter not working:
DoCmd.ApplyFilter , "Processor='Alex'"
DoCmd.ApplyFilter , "Processor='Robert'"

Please help in this regarding, how to filter the form on two/three criteria's using one field.

 

[Cronk]

DoCmd.ApplyFilter , "Processor='Alex'"
DoCmd.ApplyFilter , "Processor='Robert'"

That is not a 'multiple' filter. It is one filter replaced by the second.

Presumably you want processor as Alex OR Robert.

Use
"Processor='Alex' OR Processor = 'Robert'"

 

[gashokkumar]

Vowww! This code is working. Thanks for your assistance.

You made my day.

 

[gashokkumar]

Hello Cronk,

The code provided by you was successfully working, however, I got an new issue where another filter is not working when I apply this, below is my scenario.

I have a Main form where I click on combo button the above filter will work and displays sub form which have customer details, in this sub form where I want to filter on biweekly field after update to work on the form. But when I update the biweekly number in that field, only the below filter is working, but I need both the filters should work.

Please provide me an solution on this, that would really appreciable. Thank you!




Private Sub Combo68_AfterUpdate()
DoCmd.ApplyFilter , "[Biweekly]='" & Me.Combo68 & "'"
End Sub